Re: Find out the top 20 biggest tables

AndrewW wrote:

> Nearly.
>
> Exchange user_segments for dba_segments. user_segments only shows those
> top 20 tables owned by the user, not for the whole database.
>
> Obviously, querying dba_segments requires appropriate privileges.
>
> Rgds
> AW
>
> kirtan wrote:
>

>>Ralf Bender wrote:
>>
>>>Hi
>>>
>>>has anyone a script to find out the top 20 biggest tables in a
>>
>>tablespace?
>>
>>>regards
>>>ralf
>>
>>select * from (
>>select segment_name from user_segments where segment_type='TABLE' and
>>tablespace_name='<tablespacename>' order by bytes desc
>>) where rownum <=20

For academic purposes rewrote the above statement as:

col segment_name format a30

SELECT * FROM (
   SELECT segment_name, blocks, RANK() OVER (ORDER BY blocks DESC) SZ    FROM dba_segments
   WHERE segment_type = 'TABLE'
   AND TABLESPACE_NAME = '<tablespace_name>') WHERE SZ < 21;

If anyone wishes to look at an alternative.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)