8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Online Move of a Table in Oracle Database 12c Release 2 (12.2)
Oracle Database 12c Release 1 (12.1) introduced the ability to move table partitions and sub-partitions online. In Oracle Database 12c Release 2 (12.2) you can now perform an online move of a table, as well as individual partitions and sub-partitions.
Related articles.
- Online Table Move Operations in Oracle 12.2 Onward
- Online Move of Partitions and Sub-Partitions in Oracle 12c Release 1 (12.1)
Setup
We create and populate a test table with a primary key and secondary index. This will be used for the example move operations.
DROP TABLE t1 PURGE; -- Create table. CREATE TABLE t1 AS SELECT level AS id, 'Description for ' || level AS description, SYSDATE AS created_date FROM dual CONNECT BY level <= 1000; COMMIT; ALTER TABLE t1 ADD CONSTRAINT t1_pk PRIMARY KEY (id); CREATE INDEX t1_created_date_idx ON t1(created_date);
MOVE (offline)
In previous releases moving a table was an offline operation, and by default would a mark indexes as unusuable.
-- Offline: Basic move. ALTER TABLE t1 MOVE TABLESPACE users; -- Check indexes. SELECT index_name, status FROM user_indexes ORDER BY 1; INDEX_NAME STATUS ------------------------------ -------- T1_CREATED_DATE_IDX UNUSABLE T1_PK UNUSABLE 2 rows selected. SQL>
We can manually rebuild the indexes.
-- Manually rebuild indexes. ALTER INDEX t1_pk REBUILD ONLINE; ALTER INDEX t1_created_date_idx REBUILD ONLINE; -- Check indexes. SELECT index_name, status FROM user_indexes ORDER BY 1; INDEX_NAME STATUS ------------------------------ -------- T1_CREATED_DATE_IDX VALID T1_PK VALID 2 rows selected. SQL>
Alternatively we could include the UPDATE INDEXES
clause to manage the indexes for us.
-- Offline: Include UPDATE INDEXES to manage the indexes. ALTER TABLE t1 MOVE TABLESPACE users UPDATE INDEXES; -- Check indexes. SELECT index_name, status FROM user_indexes ORDER BY 1; INDEX_NAME STATUS ------------------------------ -------- T1_CREATED_DATE_IDX VALID T1_PK VALID 2 rows selected. SQL>
MOVE ONLINE
From Oracle 12.2 onward we can move the table as an online operation using the ONLINE
keyword. In addition to moving the table, the online move automatically maintains the indexes.
-- Online: Basic move. ALTER TABLE t1 MOVE ONLINE TABLESPACE users; -- Check indexes. SELECT index_name, status FROM user_indexes ORDER BY 1; INDEX_NAME STATUS ------------------------------ -------- T1_CREATED_DATE_IDX VALID T1_PK VALID 2 rows selected. SQL>
We can also use this feature to change table compression and storage parameters in an online operation.
-- Online: Change table compression. ALTER TABLE t1 MOVE ONLINE TABLESPACE users COMPRESS; ALTER TABLE t1 MOVE ONLINE TABLESPACE users NOCOMPRESS; -- Online: Change storage parameters. ALTER TABLE t1 MOVE ONLINE STORAGE (PCTINCREASE 0);
Restrictions
There are some restrictions associated with online moves of tables described here.
- It can't be combined with any other clause.
- It can't be used on a partitioned index-organized table or index-organized tables that have a column defined as a LOB, VARRAY, Oracle-supplied type, or user-defined object type.
- It can't be used if there is a domain index on the table.
- Parallel DML and direct path inserts are not supported against an object with an ongoing online move.
For more information see:
- ALTER TABLE
- move_table_clause
- Online Table Move Operations in Oracle 12.2 Onward
- Online Move of Partitions and Sub-Partitions in Oracle 12c Release 1 (12.1)
Hope this helps. Regards Tim...