Table size in Oracle

posted Jun 17, 2011, 7:55 AM by Sachchida Ojha
select dba_segments.owner,segment_name as table_name,sum(dba_tables.num_rows) as total_rows,sum ((bytes/1024/1024)) as total_mb from dba_segments,dba_tables
where dba_segments.owner not in ('SYSTEM','SYS','OUTLN','WMSYS','ORDSYS','MDSYS','CTXSYS','SQLTXPLAIN','XDB','DBSNMP','ADMN','SYSMAN','EXFSYS','TRCANLZR','APPQOSSYS','SH','CSMIG','PERFSTAT','TRCANLZR')
and segment_type IN ('TABLE', 'TABLE PARTITION')
and dba_tables.table_name=dba_segments.segment_name
and dba_tables.owner=dba_segments.owner
group by dba_segments.owner,segment_name
order by total_mb desc ;


select dba_segments.owner,dba_indexes.table_name,sum ((bytes/1024/1024)) as total_mb from dba_segments,dba_indexes
where dba_segments.owner not in ('SYSTEM','SYS','OUTLN','WMSYS','ORDSYS','MDSYS','CTXSYS','SQLTXPLAIN','XDB','DBSNMP','ADMN','SYSMAN','EXFSYS','TRCANLZR','APPQOSSYS','SH','CSMIG')
and segment_type IN ('INDEX', 'INDEX PARTITION')
and dba_indexes.index_name=dba_segments.segment_name
and dba_indexes.owner=dba_segments.owner
group by dba_segments.owner,dba_indexes.table_name
order by total_mb desc ;


select dba_segments.owner,dba_indexes.table_name,segment_name,sum ((bytes/1024/1024)) as total_mb from dba_segments,dba_indexes
where dba_segments.owner not in ('SYSTEM','SYS','OUTLN','WMSYS','ORDSYS','MDSYS','CTXSYS','SQLTXPLAIN','XDB','DBSNMP','ADMN','SYSMAN','EXFSYS','TRCANLZR','APPQOSSYS','SH','CSMIG')
and segment_type IN ('INDEX', 'INDEX PARTITION')
and dba_indexes.index_name=dba_segments.segment_name
and dba_indexes.owner=dba_segments.owner
group by dba_segments.owner,dba_indexes.table_name,segment_name
order by total_mb desc ;


SELECT
   owner, table_name, TRUNC(sum(bytes)/1024/1024) Meg
FROM
(SELECT segment_name table_name, owner, bytes
 FROM dba_segments
 WHERE segment_type = 'TABLE'
 UNION ALL
 SELECT i.table_name, i.owner, s.bytes
 FROM dba_indexes i, dba_segments s
 WHERE s.segment_name = i.index_name
 AND   s.owner = i.owner
 AND   s.segment_type = 'INDEX'
 UNION ALL
 SELECT l.table_name, l.owner, s.bytes
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.segment_name
 AND   s.owner = l.owner
 AND   s.segment_type = 'LOBSEGMENT'
 UNION ALL
 SELECT l.table_name, l.owner, s.bytes
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.index_name
 AND   s.owner = l.owner
 AND   s.segment_type = 'LOBINDEX')
WHERE owner in UPPER('TTT')
GROUP BY table_name, owner
HAVING SUM(bytes)/1024/1024 > 10  /* Ignore really small tables */
ORDER BY SUM(bytes) desc


SELECT owner,
segment_name,
--partition_name,
--segment_type,
sum (bytes / 1024/1024) "size_in_mb"
FROM dba_segments
WHERE segment_type IN ('TABLE', 'TABLE PARTITION')
and owner='CDR_VIEW'
group by  owner,segment_name


select


SELECT owner,
segment_name,
table_name,
--partition_name,
--segment_type,
sum (bytes / 1024/1024) "size_in_mb"
FROM dba_segments
WHERE segment_type IN ('INDEX', 'INDEX PARTITION')
and owner='CDR_VIEW'
group by  owner,segment_name,table_name
Comments