Schema size
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Schema size

  1. #1
    Join Date
    Mar 2003
    Posts
    29

    Schema size

    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.

  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938

    Re: Schema size

    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 :-)
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g
    email: ocp_9i@yahoo.com

  3. #3
    Join Date
    Mar 2003
    Posts
    29
    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

  4. #4
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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 :-)
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g
    email: ocp_9i@yahoo.com

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439

    Re: Schema size

    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width