Shrinking Database Segments Online

Prior to Oracle 10g we have MOVE command to shrink the segments, but that make the Index to UNUSABLE state, but in Oracle 10g we have SHRINK SPACE command to shrink segments without making index to UNUSABLE state.

scott@10GR2> create table t (x number,y char(2000) default '*',z char(2000) default '*');

Table created.

scott@10GR2> insert /*+ append */ into t(x)
  2  select level
  3  from dual
  4  connect by level <= 10000;

10000 rows created.

scott@10GR2> commit;

Commit complete.

scott@10GR2> create index t_ind on t(x) nologging;

Index created.

scott@10GR2> delete from t where x <= 5000;

5000 rows deleted.

scott@10GR2> commit;

Commit complete.

scott@10GR2> alter table t enable row movement;

Table altered.

scott@10GR2> SELECT INDEX_NAME,STATUS
  2  FROM USER_INDEXES
  3  WHERE TABLE_NAME ='T'
  4  /

INDEX_NAME                     STATUS
------------------------------ --------
T_IND                          VALID

scott@10GR2> alter table t move;

Table altered.

scott@10GR2> SELECT INDEX_NAME,STATUS
  2  FROM USER_INDEXES
  3  WHERE TABLE_NAME ='T'
  4  /

INDEX_NAME                     STATUS
------------------------------ --------
T_IND                          UNUSABLE

With  Oracle 10g we can use SHRINK segment rather than MOVE command.

scott@10GR2> alter table t shrink space;

Table altered.
scott@10GR2> SELECT INDEX_NAME,STATUS
  2  FROM USER_INDEXES
  3  WHERE TABLE_NAME ='T'
  4  /

INDEX_NAME                     STATUS
------------------------------ --------
T_IND                          VALID