8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | 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.

Setup
MOVE (offline)
MOVE ONLINE
Restrictions

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:

Hope this helps. Regards Tim...

Back to the Top.