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 »