Re: Find out the top 20 biggest tables

From: Mark Bole <makbo_at_pacbell.net>
Date: Fri, 18 Mar 2005 15:46:11 GMT
Message-ID: <7JC_d.11646$C47.830@newssvr14.news.prodigy.com>

Ralf Bender wrote:

> Ralf Bender said the following on 18.03.2005 08:44:
>

>> Hi
>>
>> has anyone a script to find out the top 20 biggest tables in a 
>> tablespace?
>>
>> regards
>> ralf

>
>
>
>
> Thanks a lot for the quick answers!
> :-)

The previously posted answers are a start, but to be thorough you need to also include LOB segments and table partitions, which may not all be in the same tablespace. Here is an example to find all the storage associated with a single table, including index storage (the "top 20" aspect is not included here):

col segment_name format a20
col partition_name format a20
col tablespace_name format a20

select

   ds.segment_name,
   ds.segment_type,
   ds.partition_name,
   ds.tablespace_name,
   ds.bytes,
   ds.blocks,
   ds.extents
  from dba_segments ds,
       dba_indexes di
  where di.table_name = '&&table'
    and di.owner = '&&owner'

    and di.index_name = ds.segment_name
    and di.owner = ds.owner
union
select

   ds.segment_name,
   ds.segment_type,
   ds.partition_name,
   ds.tablespace_name,
   ds.bytes,
   ds.blocks,
   ds.extents
  from dba_segments ds,
       dba_tables di
  where di.table_name = '&&table'
    and di.owner = '&&owner'

    and di.table_name = ds.segment_name
    and di.owner = ds.owner
union
select

   ds.segment_name,
   ds.segment_type,
   ds.partition_name,
   ds.tablespace_name,
   ds.bytes,
   ds.blocks,
   ds.extents
  from dba_segments ds,
       dba_lobs di
  where di.table_name = '&&table'
    and di.owner = '&&owner'

    and di.segment_name = ds.segment_name     and di.owner = ds.owner

-Mark Bole Received on Fri Mar 18 2005 - 09:46:11 CST