Mark Bole wrote:
> 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
Good ... complexity ... now what is the definition of the largest table
in a tablespace? And does it include segments not in that tablespace.
I like it when a simple request is extended to its logical conclusion.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)
Received on Fri Mar 18 2005 - 17:27:40 CST
