Administration
Reclaim wasted space (almost) online
User Rating: 
/ 1
- Details
-
Parent Category: Articles
-
Created on Wednesday, 17 March 2010 22:56
-
Last Updated on Thursday, 03 October 2013 14:39
-
Published on Wednesday, 17 March 2010 22:56
-
Written by Guy Lambregts
-
Hits: 5874
(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
