Schema size

[jluckhoff]

Got a q on the size of a schema

I just ran dbms_stats on a schema.
The db block size is 4096
a query on the dba_tables tells me there are no empty blocks on any table int the schema

I do the following sum select sum(blocks)*1024 from dba_tables where owner = 'SCHEMA_NAME'

It comes back with 531415040

I then do the following query

select count(bytes) from dba_extents where owner = 'SCHEMA_NAME'

this comes back with 958201856

?????

I would expect these two to bring back the same value for the schema size.

Does the second query take statistics and indexes in acount where the first one does not?

What am I missing and which one of the above would be a more accurate way of working out the size of a schema.

[julian]

Originally posted by jluckhoff
Got a q on the size of a schema

I just ran dbms_stats on a schema.
The db block size is 4096
a query on the dba_tables tells me there are no empty blocks on any table int the schema

I do the following sum select sum(blocks)*1024 from dba_tables where owner = 'SCHEMA_NAME'

It comes back with 531415040

I then do the following query

select count(bytes) from dba_extents where owner = 'SCHEMA_NAME'

this comes back with 958201856

?????

I would expect these two to bring back the same value for the schema size.

Does the second query take statistics and indexes in acount where the first one does not?

What am I missing and which one of the above would be a more accurate way of working out the size of a schema.

Define please "SIZE of a SCHEMA".

Shall we take into account the size of all PL/SQL packages for example? Because your queries above do not take that into account :-)

[jluckhoff]

Thanks Julian,

There are no packages triggers or procedures in this schema only a few views.

I'm only interested in finding out exactly how much disk space the schema will take up.

Thanks

j

[julian]

Originally posted by jluckhoff
Thanks Julian,

There are no packages triggers or procedures in this schema only a few views.

I'm only interested in finding out exactly how much disk space the schema will take up.

Thanks

j

Sure, you don't mean select count(bytes) from dba_extents where owner = 'SCHEMA_NAME' but select sum(bytes) from dba_extents where owner = 'SCHEMA_NAME'

That will give you a close picture but in fact you might have less data than what the query shows. You might have empty segments (that is only with a block header).

The real size of a table you get after analyzing and multiplying AVG_ROW_LEN with NUM_ROWS.

Then for each index separately run:

analyze index validate structure;

Analyze index validate structure checks the structure of the index and populates a table called index_stats. The index_stats table can only hold 1 row at a time. Then run:

select name, blocks all, lf_blks, br_blks,
blocks-(lf_blks+br_blks) empty
from index_stats;

lf_blks+br_blks are the ones really used. Multiply this with the block size.

Not very simple but not complicated :-)

[jmodic]

Originally posted by jluckhoff
I do the following sum select sum(blocks)*1024 from dba_tables where owner = 'SCHEMA_NAME'

It comes back with 531415040

I then do the following query

select count(bytes) from dba_extents where owner = 'SCHEMA_NAME'

this comes back with 958201856

?????

I would expect these two to bring back the same value for the schema size.

Column BLOCKS (and BYTES for that matter) in DBA_SEGMENTS/DBA_EXTENTS shows totaly different thing than column BLOCKS (BYTES) in DBA_TABLES for the same table. In DBA_SEGMENTS it shows the total space occupied by that segment (table, index, ...), while in DBA_TABLES shows only the space below the high watter mark for that table. Check the following thread: http://www.dbasupport.com/forums/sho...threadid=39062

Also, your query on DBA_EXTENTS counts both tables and indexes, while the query against DBA_TABLES relates to tables only, without the corresponding indexes.