Reorganize the table with Shrink segment command
Benefit of doing the table reorganization with Shrink command is that table is available online. it has exclusive lock for HVM movement
and expected some delay in transactions. But you can perform this operation online.
It is very Simple to use and no additional space is required since data is compacted using insert/delete pairs.
Indexes can be automatically re-organized using the CASCADE option. You can specify cascade with the Statements for reorganize the related objects.
Following are the steps involved in reorganize the table:
1. Enable row movement for the table.
SQL> ALTER TABLE table name ENABLE ROW MOVEMENT;
2. Shrink table but don’t want to shrink HWM (High Water Mark).
SQL> ALTER TABLE table name SHRINK SPACE COMPACT;
3. Shrink the tables or objects associated with it.
---Shrink table and HWM too.
SQL> ALTER TABLE table name SHRINK SPACE;
--Shrink table and all dependent index too.
SQL> ALTER TABLE table name SHRINK SPACE CASCADE;
--Shrink table under MView.
SQL> ALTER TABLE table name SHRINK SPACE;
--Shrink Index only.
SQL> ALTER INDEX index name SHRINK SPACE;
4. Disable the row movement for the table.
SQL> ALTER TABLE table name DISABLE ROW MOVEMENT;
Pingback: Reorganize a table in Oracle database with CTAS method | Smart way of Technology