exact size in bytes consumed by a table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: exact size in bytes consumed by a table

  1. #1
    Join Date
    Apr 2001
    Location
    Berlin
    Posts
    65

    Post

    Is there any way that I can determine the exact bytes (size) that a table has used so far instead of looking at the bytes columns of the dba_segments view and also without caculating the average row length? The bytes column from dba_segments only gives me the size(in bytes) based on the initial_extent(and or next_extent) specified during the table creation.

    I would like to determine the actual size(in bytes) of the table not based on the initial and the next extents specified during table creation

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    No, DBA_SEGMENTS.BYTES gives you exatly the size that table occupies in a tablespace. No more, no less. You can't get more acurate tablesize anywhere, because this information is 100% accurate.

    Why do you think it doesn't show accurate size?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Apr 2001
    Location
    Berlin
    Posts
    65
    I don't think so. This is why I'm saying so. Assuming that you have an initial_extent of 100M for a table and only 1 record is entered into that table.

    Now when you select initial_extent,bytes from dba_segments for that table, the bytes column would be 100M while the initial_extent would be 100M as well.

    Check out this example and let me know what you think:

    SQL> create table GARDEN
    (baby varchar2(13),
    man varchar2(12))
    storage (initial 1M next 1M)

    SQL> Table created.

    SQL> commit;

    Commit complete.

    SQL> insert into garden (baby,man) values (02,03);

    1 row created.

    SQL> commit;

    Commit complete.

    SQL> select initial_extent,bytes from dba_segments where segment_name in ('GARDEN');

    INITIAL_EXTENT BYTES
    -------------- ---------
    1048576 1054720

    SQL>

    I'm sure you can now see that the single record that I inserted is less that 1M.

    Any thoughts?

  4. #4
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Hi,

    I think he wants to find out how many bytes are actually used by the table (occupied by rows in the table) not the bytes allocated to the table (bytes column in dba_segments)

    To find out exactly how much space is used out of the allocated bytes. Follow the steps.

    1.SELECT count(distinct(substr(rowid,15,4)||substr (rowid,1,8) ))
    FROM table_name;

    This query will return a number that is the number of blocks used by the table.

    2. Multiply this number by your db_block_size.

    The outcome will be the exact space used by the table, not the allocated space to the table.

    P.S.
    This query will work for Oracle7 only because the ROWID format is different for Oracle8.
    But the same concept can be used to modify the query and find out the blocks used in Oracle8 as well.

    Cheers.
    Sanjay




  5. #5
    Join Date
    Jan 2002
    Posts
    3
    In oracle 8 use of ROWID_BLOCK_NUMBER attribute
    of DBMS_ROWID package will help you do get the distinct block.

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by oracleman
    I'm sure you can now see that the single record that I inserted is less that 1M.
    Any thoughts?
    So you've put a wrong question and a wrong thread subject. Table is occupying exactly as many bytes in your tablespace as it is shown in DBA_EXTENTS. Period.

    If you want to know how much space *inside that table* is actually occupied by existing rows - that is whole different story. And to answer it in short - There is no way to determine that, appart for dumping each and every data block of that table and examine its contents. We have discussed this isue many time in this forum - belive me, you generaly can't determine exact amount of occupied blocks in a table.

    The closest thing you could get as an answer to your question is to determine the highwatter mark (HWM) of a table - that is the last block that has ever been occupied in the table since it was created or truncated last time.
    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