Moving a Table to a New Segment or Tablespace

•The ALTER TABLE...MOVE statement enables you to relocate data of a non-partitioned table or of a partition of a partitioned table into a new segment, and optionally into a different tablespace.

•This statement also lets you modify any of the storage attributes of the table or partition, including those which cannot be modified using ALTER TABLE.

•It is good to remember that The ALTER TABLE...MOVE statement does not permit DML against the table while the statement is executing. If you want to leave the table available for DML while moving it it the you have to use DBMS_REDEFINITION package to move online.

•Moving a table changes the rowids of the rows in the table. This causes indexes on the table to be marked UNUSABLE, and DML accessing the table using these indexes will receive an ORA-01502 error. The indexes on the table must be dropped or rebuilt. Likewise, any statistics for the table become invalid and new statistics should be collected after moving the table.

•You cannot move a table containing a LONG or LONG RAW column.

•You cannot MOVE an entire partitioned table. You must move individual partitions or subpartitions.

Example:
---------------
SQL> select TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK from dba_segments where owner='ARJU' and segment_name='TEST';

TABLESPACE_NAME HEADER_FILE HEADER_BLOCK
------------------------------ ----------- ------------
USER_TBS 11 1283

Here HEADER_BLOCK indicates the ID of the block containing the segment header. And HEADER_FILE is the data file id. If I move the table to a new segment then header block number will be change. Lets have a look at it.

SQL> alter table test move;
Table altered.

SQL> select TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK from dba_segments where owner='ARJU' and segment_name='TEST';

TABLESPACE_NAME HEADER_FILE HEADER_BLOCK
------------------------------ ----------- ------------
USER_TBS 11 1459

So HEADER_BLOCK change to 1459 from 1283.

Now I want to move the table test from USER_TBS tablespace to tablespace TBS_AFTER_BACKUP. To see so we have to append TABLESPACE keyword like,

SQL> alter table test move tablespace TBS_AFTER_BACKUP;
Table altered.

SQL> select TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK from dba_segments where owner='ARJU' and segment_name='TEST';
TABLESPACE_NAME HEADER_FILE HEADER_BLOCK
------------------------------ ----------- ------------
TBS_AFTER_BACKUP 6 11