This chapter describes how to use some of the new features related to moving a table to a new segment or tablespace.
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:
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.
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:
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.
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_2 tablespaces must exist.