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.

Given that you've already been informed your query won't work quite as you expected converting from blocks to bytes is fairly straightforward:

bytes := blocks * db_block_size

So taking your query and making it work as you expect and having it return bytes rather than blocks:

select t.table_name, t.tablespace_name, t.blocks*p.value bytes from
(select table_name, tablespace_name, blocks  from dba_tables
 order by blocks desc) t,
(select value
 from v$parameter
 where name = 'db_block_size') p
where rownum <21;

David Fitzjarrell Received on Wed Dec 13 2006 - 23:47:22 CST