Segment Space Advisor in Oracle 10g

scott@9IR2> create table t as select * from all_objects;

Table created.

Elapsed: 00:00:02.63
scott@9IR2> drop table t;

Table dropped.

Elapsed: 00:00:00.12
scott@9IR2> create table t TABLESPACE DATA1 as select * from all_objects;

Table created.

Elapsed: 00:00:01.15
scott@9IR2> exec show_space(USER,'T','TABLE');
l_free_blks*******************  0
l_total_blocks****************  512
l_total_bytes*****************  4194304
l_unused_blocks***************  59
l_unused_bytes****************  483328
l_last_used_extent_file_id****  11
l_last_used_extent_block_id***  148745
l_last_used_block*************  69

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
scott@9IR2> DELETE FROM T WHERE ROWNUM <= 20000;

20000 rows deleted.

Elapsed: 00:00:00.71
scott@9IR2> COMMIT;

Commit complete.

Elapsed: 00:00:00.01
scott@9IR2> exec show_space(USER,'T','TABLE');
l_free_blks*******************  274
l_total_blocks****************  512
l_total_bytes*****************  4194304
l_unused_blocks***************  59
l_unused_bytes****************  483328
l_last_used_extent_file_id****  11
l_last_used_extent_block_id***  148745
l_last_used_block*************  69

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03

Now we could see a lot of Free Blocks  under HWM. However to reset HWM we need to use ALTER TABLE MOVE command (don't forget to Rebuild Index after MOVE since Index remains in UNUSABLE state).In 10g, this task has become trivial; you can now use Inputs from Segment Space Advisor to shrink segments, tables, and indexes using SHRINK SPACE command to reclaim free blocks.
but no need to manually rebuild the Index.


scott@10G> create table T
  2  as
  3  select *
  4  from all_objects , (select level from dual connect by level <=&num);
Enter value for num: 2
old   4: from all_objects , (select level from dual connect by level <=&num)
new   4: from all_objects , (select level from dual connect by level <=2)

Table created.

Elapsed: 00:00:03.14
scott@10G> delete from t;

102706 rows deleted.

Elapsed: 00:00:03.40
scott@10G> commit;

Commit complete.

Elapsed: 00:00:00.00

scott@10G> column recommendations format a80;
scott@10G> SELECT ROUND((allocated_space/1024/1024),2) AS Allocated_mb,
  2    ROUND((used_space          /1024/1024),2) AS Used_mb,
  3    ROUND((reclaimable_space   /1024/1024),2) AS Reclaim_mb,
  4    recommendations
  5  FROM TABLE(dbms_space.asa_recommendations)
  6  WHERE segment_owner ='SCOTT'
  7  AND segment_name    ='T'
  8  AND segment_type    ='TABLE'
  9  /

ALLOCATED_MB    USED_MB RECLAIM_MB RECOMMENDATIONS
------------ ---------- ---------- --------------------------------------------------------------------------------
    53656.82   32219.14   21437.68 Enable row movement of the table SCOTT.T and perform shrink, estimated savings i
                                   s 22479040749 bytes.


Elapsed: 00:00:00.00