Re: newbie question: size of schemas sql

Hi Winbatch,

It's not necessarily a simple question although some clarification on what precisely you mean by size of schema is still required

Do you mean just the sum of all the object data or do you mean the sum of all data below the HWM (hence including 'wasted' space within blocks such as block headers, free space, etc)? By schema, do you also want to include space used by indexes, views, MV, sequences, stored programs, etc. ?

If you just want used space below the HWM, then the dbms_space.space_usage package should give you the details you're after.

If you want actual data only, then compute statistics and multiple the avg_row_len by the num_rows should give you a pretty close total. Similar with indexes and index_stats.

If you want space used by other object types, then it gets a little trickier depending on object type.

So it depends somewhat on what you want and what you want it for.

Cheers

Richard

Winbatch wrote:
>
> Hi,
> This may be a simple question. How do I find out the size of each schema in
> the database? (I don't mean the potential or allotted size, but the actual
> size of the schemas in the db). I have system level access so any/all sql
> can be run.
>
> Thanks,
> Winbatch
Received on Mon Aug 19 2002 - 07:08:08 CEST