Feeds:
Posts
Comments

Posts Tagged ‘claim space’

Shrink Database Object

Why Shrink Objects?

You want to shrink the Database objects in order to do claim unused disk for objects (tables and Index)

How to Shrink Objects?

In order to shrink objects, Objects needs to be enabled with row movement first (By Default they are Disabled).

Query below to see if the table is enabled for row movement

select table_name, row_movement from user_tables;

ALTER TABLE employee ENABLE ROW MOVEMENT;

Following are 3 options…

1) Recover space and amend the high water mark (HWM).
ALTER TABLE employee SHRINK SPACE;
2) Recover space, but don’t amend the high water mark (HWM).
ALTER TABLE employee SHRINK SPACE COMPACT;
3) Recover space for the object and all dependant objects.
ALTER TABLE employee SHRINK SPACE CASCADE;

Finally,
make sure to Disable the row movement (if this was the case at the beginning)
ALTER TABLE employee DISABLE ROW MOVEMENT;

Read Full Post »