Код:
select segment_name TableName,
sum(bytes) TotalBytes,
sum(case when ObjectType='TABLE' then bytes end) TableBytes,
sum(case when ObjectType='INDEX' then bytes end) IndexBytes,
sum(case when ObjectType='LOB' then bytes end) LobBytes,
sum(case when ObjectType='NESTED' then bytes end) NestedBytes
from (--Собственно, таблицы.
select s.segment_name,sum(bytes) Bytes,'TABLE' ObjectType
from dba_segments s
where s.segment_type in ('TABLE','TABLE PARTITION')
and s.owner = 'SUPERMAG'
group by s.segment_name
union all
-- Для учета индексов
select i.table_name,sum(bytes) Bytes,'INDEX' ObjectType
from dba_segments s,
dba_indexes i
where s.segment_type in ('INDEX','INDEX PARTITION')
and i.index_name = s.segment_name
and i.owner = s.owner
and i.table_owner = 'SUPERMAG'
group by i.table_name
union all
-- Для учета LOB-колонок
select t.table_name,sum(bytes) Bytes,'LOB' ObjectType
from dba_segments s,
dba_lobs l,
dba_lobs li,
dba_tables t
where s.owner = 'SUPERMAG'
and s.segment_type like 'LOB%'
and l.segment_name(+) = s.segment_name
and li.index_name(+) = s.segment_name
and t.table_name = nvl(l.table_name,li.table_name)
and t.owner = s.owner
group by t.table_name
union all
-- Для учета NESTED-TABLES
select nt.parent_table_name,sum(bytes) Bytes,'NESTED' ObjectType
from dba_segments s,
dba_nested_tables nt
where s.segment_type like 'NEST%'
and s.owner='SUPERMAG'
and nt.owner = s.owner
and nt.table_name = s.segment_name
group by nt.parent_table_name)
group by segment_name взято тут