Reorganize the table with Shrink segment command

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;

1 thought on “Reorganize the table with Shrink segment command

  1. Pingback: Reorganize a table in Oracle database with CTAS method | Smart way of Technology

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.