Square DBA - Oracle DBA Talk

Shrink table segment that experienced large delete

Segments that undergo significant data manipulation language (DML) activity, such as UPDATE and DELETE operations, can become sparsely populated, with chunks of free space within their data blocks. Besides simply wasting space, sparsely populated segments can also lead to poor performance, because operations such as a full table scan will need to scan more blocks than necessary to retrieve data.

Before Oracle Database 10g, you could reclaim the free segment space by dropping the table, re-creating it, and then reloading the data. You could also use the ALTER TABLE MOVE command to move the table to a different tablespace. Both of these processes, however, must occur with the table offline. Online table reorganization is another way to reclaim space, but it requires significant disk space. With Oracle Database 10g, you can now shrink segments directly, without taking the tablespaces or the objects offline. The process of shrinking a segment :

1. Enable row movement : ALTER TABLE scott.emp ENABLE ROW MOVEMENT;
2. Recover space and amend the high water mark (HWM): ALTER TABLE scott.emp SHRINK SPACE; OR
3. Recover space, but don’t amend the high water mark (HWM) : ALTER TABLE scott.emp SHRINK SPACE COMPACT;
4. Recover space for the object and all dependant objects : ALTER TABLE scott.emp SHRINK SPACE CASCADE;

Rebuild indexes & gather statistics after table shrink.

Just a quick tip.  When you finish a table segment shrink, it is a good practice to rebuild any indexes on that table as well as gather statistics on that table. These tasks help Oracle to create a better execution plan for the SQL query related to the table.