isitdevops.com/databases

— schema_size_sum.sql
— mdw 17/10/05
— summary of the size of the database (as measured in all sorts of ways)
set pages 32
set pause off
col total_dbf_mb form 999,999,999
col dat_dbf_mb form 999,999,999
col ind_dbf_mb form 999,999,999
col total_seg_mb form 999,999,999
col table_seg_mb form 999,999,999
col index_seg_mb form 999,999,999
col LOB_seg_mb form 999,999,999
spool schema_size_sum.lst
select owner,sum(bytes/(1024*1024)) total_seg_MB
from dba_segments
where segment_type not in (‘TEMPORARY’,’ROLLBACK’,’CACHE’)
and owner not in (‘SYS’,’SYSTEM’)
group by owner
order by owner
/
select owner,sum(bytes/(1024*1024)) table_seg_MB
from dba_segments
where segment_type in (‘TABLE’,’TABLE PARTITION’)
and owner not in (‘SYS’,’SYSTEM’)
group by owner
order by owner
/
select owner,sum(bytes/(1024*1024)) index_seg_MB
from dba_segments
where segment_type in (‘INDEX’,’INDEX PARTITION’)
and owner not in (‘SYS’,’SYSTEM’)
group by owner
order by owner
/
select owner,sum(bytes/(1024*1024)) LOB_seg_MB
from dba_segments
where segment_type in (‘LOBINDEX’,’LOBSEGMENT’,’LOB PARTITION’)
and owner not in (‘SYS’,’SYSTEM’)
group by owner
order by owner
/
–where cole like nvl(upper(‘ampersand’),’WHOOPS’)||’%’
spool off
clear col
—
— EOF
—