Oracle Used space in Meg by segment type
Oracle Table Size
set pagesize 200 linesize 200
col segment_name for a30
select * from
(
select
owner,
segment_name,
segment_type,
trunc(bytes/1024/1024/1024,2) gb,
tablespace_name
from dba_segments
order by 4 desc
) where
rownum <= 30
set pagesize 100 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
set linesize 200
select a.owner, a.table_name, trunc(b.size_gb,2) size_gb, trunc (((a.blocks*8192/1024/1024/1024)-(a.num_rows*avg_row_len/1024/1024/1024)) ,2) as actual_gb,trunc ((b.size_gb-((a.blocks*8192/1024/1024/1024)-(a.num_rows*avg_row_len/1024/1024/1024))),2) savings,
a.tablespace_name, b.segment_type table_type, a.last_analyzed from dba_tables a, (select * from (select owner, segment_name, segment_type, tablespace_name, sum(bytes/1024/1024/1024) size_gb from dba_segments
where owner='&schema_name'
-- and segment_type like '%TABLE%'
group by segment_name, owner, segment_type, tablespace_name
order by 5 desc)
where rownum<11) b
where a.table_name=b.segment_name and a.owner=b.owner
order by size_gb desc;
