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.
