Re: Retrieve list of n largest tables in a db

ozonefilter_at_gmail.com wrote:
> EdStevens wrote:
> > First, you'll need to specify the criteria used to define 'table size'.
> > Are you measuring by the number of rows? Extents? Blocks? Bytes?
>
> Hi Ed,
>
> thanks for your answer. What I mean by 'table size' is the amount of
> data stored (in bytes).
>
> > Look at the definition of the view DBA_TABLES (or USER_TABLES or
> > ALL_TABLES)
>
> I've looked at their definition, and I'm now considering the following
> query:
>
> SELECT table_name, tablespace_name, blocks FROM DBA_TABLES
> WHERE ROWNUM <= 20
>
> Given that I don't need extreme accuracy, I assume that using blocks,
> the results are still indicative enough even in terms of bytes used.
> Will this query work well and is my assumption correct?
>
> Thanks.

The query wont exactly work, you'll have to order your results in descending order:

SELECT * FROM
(

    SELECT table_name, tablespace_name, blocks     FROM DBA_TABLES
    ORDER BY blocks DESC
)
WHERE ROWNUM < 21 Received on Wed Dec 13 2006 - 19:19:20 CST