Honey, I shrunk the table!

Let us say , you  have a table where there are frequent deletes / inserts  and your application reads this table ( via full table scan )  for processing the business results . You are  likely  to have performance issue , as Oracle has to read all the empty blocks till HWM .  In 10g and above , you can reset the HWM as below .

SQL> drop table t ;
Table dropped
SQL> create table t as select * from all_objects;
Table created
SQL> select sum(bytes) from user_segments where segment_name = 'T';
SUM(BYTES)
----------
99614720


SQL> select count(*) from t;
COUNT(*)
----------
629079


SQL> delete from t where owner ='SYS';
23157 rows deleted

SQL> select count(*) from t;
COUNT(*)
----------
605922


SQL> select sum(bytes) from user_segments where segment_name = 'T';
SUM(BYTES)
----------
99614720



Even after deleting few records , the space is not released.
But , when you shrink the table as below , you can reclaim the space.

 

SQL> alter table t ENABLE row movement ;
Table altered
SQL> alter table t shrink space compact;
Table altered
SQL> alter table t shrink space;
Table altered

SQL> select sum(bytes) from user_segments where segment_name = 'T';
SUM(BYTES)
----------
88932352

If you are not sure of which objects can be the candiates for shrinking , you can refer to the findings from segment advisor . Either you can use segment advisor from EM or PL /SQL . You can get the relevant sql commands as shown below .


Select * from

(
select c3 from table (dbms_space.asa_recommendations())
union ALL
select c2 from table (dbms_space.asa_recommendations())
union all
select c1 from table (dbms_space.asa_recommendations())
)
where c3 is not null

dbms_space.asa_recommendations()  provides the estimated space savings . I have asked the accuracy of this value to Tom Kyte in his forum . Please see his response at
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2049277600346543592#2049993400346447818

Please send in your comments.