19 | 10 | 2017
Latest Articles
Popular Articles

Administration

Reclaim wasted space (almost) online

User Rating:  / 0
PoorBest 

(Almost) Online space recovery ( reset HWM )

 

See here If you are using 11G and would like to reclaim temp space online

 

Huge data was deleted but space is not reclaimed since we used the "delete from" syntax  

 

DB_USER >select table_name,blocks from dba_tables where owner='ADMIN' and table_name='TEXT_DATA';

TABLE_NAME                         BLOCKS

TEXT_DATA                          428291


There are even though no records in the table left


DB_USER >select count(*) from admin.text_data;
 
COUNT(*)

  0  

DB_USER >desc admin.text_data;  

Name                                      Null?    Type  

----------------------------------------- -------- ----------------------------  

ID                                        NOT NULL NUMBER(12)  

ID_1                                      NOT NULL NUMBER(12)  

ID_2                                      NOT NULL NUMBER(12)  

TEXT                                               CLOB  

DATE_UPDATED                              NOT NULL DATE

 

 

New in 10G and usefull for online table space reclaim ( as online as possible )    

 

DB_USER >alter table admin.text_data enable row movement;  

Table altered.  

 

See also PL/SQL invalidations when you enable row movement at table level

/* The shrink space cascade compact does not reset the HWM ( does not invalidate sql cursors , requires undo blocks, so undo_retention and undo space comes into play ) */  

 

DB_USER >alter table admin.text_data shrink space cascade compact;  

Table altered.  

 

/* The shrink space cascade compact does reset the HWM ( fast if the above was done but invalidates sql cursors, is not really online ) */  

 

DB_USER >alter table admin.text_data shrink space cascade;  

Table altered.  

 

There we are

 

DB_USER >exec dbms_stats.gather_table_stats('ADMIN','TEXT_DATA',cascade=>true);  

PL/SQL procedure successfully completed.  

 

DB_USER >select table_name,blocks from dba_tables where owner='ADMIN' and table_name='TEXT_DATA';

TABLE_NAME                         BLOCKS

TEXT_DATA                           1

 

We can use the online shrink technology at the partition level, note we use the below listed commands for such case

 

alter table &owner.&table_name modify partition &partition_name shrink space cascade

alter table &owner.&table_name modify partition &partition_name shrink space compact cascade