Restrictions for Shrinking Table

To Shrink Table -
1) Make sure that there are no ROWID based Materialized view exists, if so, drop them.
 As Shrink table may change the rowids, ROWID based MV must be dropped
2) Make sure that no Function based indexes exist
3) Enable Table row movement, if it is disabled
 alter table <table_name> enable row movement
4) Execute Shrink command
 alter table <table_name> shrink space;
5) if you had Table row movement disabled, put it back
 alter table <table_name> disable row movement
6) Re-Create function based indexes that were dropped earlier
7) Re-Create ROWID based Materialized view that was dropped in earlier

Read Full Post »

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 »