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.