RE: How to calculate size of table including size of its indexes too.

From: Michael McMullen <ganstadba_at_hotmail.com>
Date: Tue, 22 Jan 2008 13:28:03 -0500
Message-ID: <BAY141-DAV6750D9F0809634DDE13B2A63E0@phx.gbl>
Message-ID: <003001c85d24$862e0d70$d17810ac@bell.corp.bce.ca>

Something like this as a "start". I just did it for one table to test, and you would need to use an analytical/case/decode to get only one value for the table segment.
I said this is a "start". It will return single-row subquery errors for partitioned tables and not return anything if a table doesn't have indexes. So maybe not much help at all.

select z.segment_name,z.table_bytes,z.index_name,(select c.bytes/1024/1024

                                         from
                                         dba_segments c
                                         where z.index_name=

c.segment_name)index_bytes
from
(
select a.segment_name,a.bytes/1024/1024 table_bytes,b.index_name from
dba_segments a, dba_indexes b
where
segment_name = 'MY_SEGMENT'
and a.owner = 'MY_OWNER'
and
a.segment_name = b.table_name
) z
order by 1,3  

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jan 22 2008 - 12:28:03 CST