Oracle Used space in Meg by segment type

set pagesize 50000
set line 80

col "Total Used Meg" format 999,999,990
col "Data part" format 999,999,990
col "Index part" format 999,999,990
col "LOB part" format 999,999,990
col "RBS part" format 999,999,990
tti 'Used space in Meg by segment type'

select sum(bytes)/1024/1024 "Total Used",sum( decode( substr(segment_type,1,5), 'TABLE', bytes/1024/1024, 0)) "Data part",
sum( decode( substr(segment_type,1,5), 'INDEX', bytes/1024/1024, 0)) "Index part",
sum( decode( substr(segment_type,1,3), 'LOB', bytes/1024/1024, 0)) "LOB part",
sum( decode(segment_type,'ROLLBACK', bytes/1024/1024, 0)) "RBS part",
sum( decode(segment_type,'TEMPORARY', bytes/1024/1024, 0)) "TEMP part"
from sys.dba_segments
/

tti off

tti "Total database size"

select sum(bytes)/1024/1024 "Total DB size in Meg" from sys.v_$datafile
/
tti off



SQL> @used_space.sql

Tue Sep 13 page 1
Used space in Meg by segment type

Total Used Data part Index part LOB part RBS part TEMP part
---------- ------------ ------------ ------------ ------------ ----------
2060.75 1,218 490 274 0 0


Tue Sep 13 page 1
Total database size

Total DB size in Meg
--------------------
2728.75