Table, Schema & Database size

 1) Schema size

set linesize 150

set pagesize 5000

col owner for a15

col segment_name for a30

col segment_type for a20

col TABLESPACE_NAME for a30

clear breaks

clear computes

compute sum of SIZE_IN_GB on report

break on report

select OWNER,sum(bytes)/1024/1024/1000 “SIZE_IN_GB” from dba_segments group by owner order by 2;

select OWNER,sum(bytes)/1024/1024/1000 “SIZE_IN_GB” from dba_segments where owner='HR' group by owner;

Find Top Schemas by Size in Oracle

SELECT * FROM (

SELECT OWNER, SUM(BYTES)/1048576 MB 

from DBA_SEGMENTS 

GROUP BY OWNER 

ORDER BY MB DESC)

WHERE ROWNUM < 20;

2) Table size

select segment_name,segment_type,round(SUM(BYTES)/power(2,20)) SEGMENT_SIZE_MB from dba_segments where segment_type='TABLE' and owner='TABLE_OWNER' and segment_name='<TABLE_NAME>';

Find Top Tables by Size in Oracle

SELECT * FROM (

SELECT SEGMENT_NAME, round(SUM(BYTES)/power(2,20)) SEGMENT_SIZE

FROM DBA_SEGMENTS where segment_type='TABLE' 

GROUP BY SEGMENT_NAME

ORDER BY 2 desc)

WHERE ROWNUM < 101;

3) Database Size

select /*+parallel(4) */ round(sum(bytes / (1024*1024*1024))) "DB Size in GB" from dba_segments;

select /*+parallel(4) */ round(sum(bytes / (1024*1024*1024))) "DB Size in GB" from dba_data_files;