Actual space consumed by an Oracle Object

When asked “What is the size of the table ‘A’ ?”, we often check only that segments size.

select sum(bytes)/1024/1024 from dba_segments where segment_name='A' and segment_type='TABLE';

This gives the total segment size. It could be that the table is either full, sparsely populated or even empty, still there would not be any change for the segment size. We could assign 1GB for a table which would actually need only 10MB, but who lose? Assigning more(very much extra) space than necessary has negative impact not only on space, but also on the performance. Each time a full table scan is performed, oracle retrieves all the blocks until the High Water Mark of the table. It could be that most of these blocks are empty. The same for indexes also.  The segment advisor suggests the candidate tables for shrink operation or you could manually select your known tables and check its actual consumed space using dbms_space.unused_space() package.

spool d:\oracle\table.log

DECLARE
 alc_bks     NUMBER;
 alc_bts     NUMBER;
 unsd_bks    NUMBER;
 unsd_bts    NUMBER;
 luefi       NUMBER;
 luebi       NUMBER;
 lub         NUMBER;
BEGIN
 FOR c1_row IN (SELECT table_name
 FROM   dba_tables where owner='JJ') LOOP
 DBMS_SPACE.UNUSED_SPACE (
 segment_owner               => 'JJ'
 ,   segment_name                => c1_row.table_name
 ,   segment_type                => 'TABLE'
 ,   total_blocks                => alc_bks
 ,   total_bytes                 => alc_bts
 ,   unused_blocks               => unsd_bks
 ,   unused_bytes                => unsd_bts
 ,   last_used_extent_file_id    => luefi
 ,   last_used_extent_block_id   => luebi
 ,   last_used_block             => lub
 );
DBMS_OUTPUT.PUT_LINE( c1_row.table_name || ',' || alc_bts ||','|| unsd_bts );
 END LOOP;
EXCEPTION
 WHEN OTHERS THEN
 DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,250));
END;
/

PS:i didn't write the below script, but modified to separate output by a delimit.
Advertisements

3 thoughts on “Actual space consumed by an Oracle Object

  1. Nice post. I learn something more challenging on different blogs everyday. It will always be stimulating to read content from other writers and practice a little something from their store. I�d prefer to use some with the content on my blog whether you don�t mind. Natually I�ll give you a link on your web blog. Thanks for sharing.

  2. Pingback: Index | My confrontations with oracle

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s