7 Moving a Table to a New Segment or Tablespace

This chapter describes how to use some of the new features related to moving a table to a new segment or tablespace.

About Moving a Table to a New Segment or Tablespace

The ALTER TABLE...MOVE [PARTITION|SUBPARTITION] statement enables you to move a table, partition, or subpartition to change any physical storage attribute, such as compression, or the tablespace, assuming you have the appropriate quota in the target tablespace.

ALTER TABLE...MOVE statements support the ONLINE keyword, which enables data manipulation language (DML) operations to run uninterrupted on the table, partition, or subpartition that is being moved. The following statements move a table, partition, or subpartition online:

  • ALTER TABLE ... MOVE ... ONLINE

  • ALTER TABLE ... MOVE PARTITION ... ONLINE

  • ALTER TABLE ... MOVE SUBPARTITION ... ONLINE

Moving a table changes the rowids of the rows in the table. If you move a table and include the ONLINE keyword and the UPDATE INDEXES clause, then the indexes remain usable during the move operation. If you include the UPDATE INDEXES clause but not the ONLINE keyword, then the indexes are usable immediately after the move operation. The UPDATE INDEXES clause can only change the storage properties for the global indexes on the table or storage properties for the index partitions of any global partitioned index on the table. If you do not include the UPDATE INDEXES clause, then the changes to the rowids cause the indexes on the table to be marked UNUSABLE, and DML accessing the table using these indexes receive an ORA-01502 error. In this case, the indexes on the table must be dropped or rebuilt.

A move operation causes any statistics for the table to become invalid, and new statistics should be collected after moving the table.

If the table includes LOB column(s), then this statement can be used to move the table along with LOB data and LOB index segments (associated with this table) that are explicitly specified. If not specified, then the default is to not move the LOB data and LOB index segments.

Moving a Table

Use the ALTER TABLE...MOVE statement to move a table to a new segment or tablespace.

When you use the ONLINE keyword with this statement, data manipulation language (DML) operations can continue to run uninterrupted on the table that is being moved. If you do not include the ONLINE keyword, then concurrent DML operations are not possible on the data in the table during the move operation.

To move a table:

  1. In SQL*Plus, connect as a user with the necessary privileges to alter the table.

    See Oracle Database SQL Language Reference for information about the privileges required to alter a table.

  2. Run the ALTER TABLE ... MOVE statement.

Example 7-1 Moving a Table to a New Tablespace in Online Mode

The following statement moves the hr.jobs table online to a new segment and tablespace, specifying new storage parameters. The ONLINE keyword means that DML operations can run on the table uninterrupted during the move operation. The hr_tbs tablespace must exist.

ALTER TABLE hr.jobs MOVE ONLINE
      STORAGE ( INITIAL 20K
                NEXT 40K
                MINEXTENTS 2
                MAXEXTENTS 20
                PCTINCREASE 0 )
  TABLESPACE hr_tbs;

Example 7-2 Moving a Table and Updating the Table’s Indexes

Assume the following statements created a table and its indexes:

CREATE TABLE dept_exp (
     DEPTNO NUMBER (2) NOT NULL,
     DNAME VARCHAR2 (14),
     LOC VARCHAR2 (13)) 
   TABLESPACE tbs_1;

CREATE INDEX i1_deptno ON dept_exp(deptno) TABLESPACE tbs_1;
CREATE INDEX i2_dname ON dept_exp(dname) TABLESPACE tbs_1;

The following statement moves the table to a new tablespace (tbs_2) and compresses the table. It also moves index i2_dbname to tablespace tbs_2 and specifies that both the i1_deptno index and the i2_dname index are usable after the move operation.

ALTER TABLE dept_exp MOVE 
   COMPRESS TABLESPACE tbs_2
   UPDATE INDEXES
      (i1_deptno TABLESPACE tbs_1,
       i2_dname TABLESPACE tbs_2);

Notice that this statement does not include the ONLINE keyword. However, the ONLINE keyword is supported if DML operations must be able to run on the table uninterrupted during the move operation, or if the indexes must be usable during the move operation.

Before running these statements, the tbs_1 and tbs_2 tablespaces must exist.