Oracle compact table

Surely more than once have deleted records from a table in large quantities, perhaps millions. Once this is done it expected would be a reduction of the space used in the tablespaces, but it does not. Extensions but remain free to use the given table, can not use another table.

After the removal of millions of records, you must compact the table to return to tablespace free space once done we can even reduce the size of the datafiles without meeting the limitation imposed by the HIGH WATER MARK (maximum size reached by the objects within a datafile).

Suppose the table:

Source   
scott.test

We must do two things to compact the table and return the free space: activate the movement of rows and compaction/shrinkage of the table.

Source   
ALTER TABLE scott.test ENABLE ROW MOVEMENT;
ALTER TABLE scott.test SHRINK SPACE;

An important detail to consider is that then you have to rebuild all indexes on the table:

Source   
ALTER INDEX  scott.idx1_test REBUILD;

This last step is important, after compacting the rowid of the table have changed, think a rowid is the physical description of where the row is and indexes stored ROWIDs.

Leave a Reply