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.

MOVE ONLINE

The following example shows how to online move a table.

-- Create table.

DROP TABLE t1 PURGE;

CREATE TABLE t1

(id            NUMBER,

 description   VARCHAR2(50),

 created_date  DATE,

 CONSTRAINT t1_pk PRIMARY KEY (id)

);

-- Populate it.

INSERT INTO t1

SELECT level,

       'Description for ' || level,

       CASE

         WHEN MOD(level,2) = 0 THEN TO_DATE('01/07/2015', 'DD/MM/YYYY')

         ELSE TO_DATE('01/07/2016', 'DD/MM/YYYY')

       END

FROM   dual

CONNECT BY level <= 1000;

COMMIT;

EXEC DBMS_STATS.gather_table_stats(USER, 't1');

We can now move the table using the ONLINE keyword.

-- Basic move.

ALTER TABLE t1 MOVE ONLINE TABLESPACE users;

-- Change table compression.

ALTER TABLE t1 MOVE ONLINE TABLESPACE users COMPRESS UPDATE INDEXES;

ALTER TABLE t1 MOVE ONLINE TABLESPACE users NOCOMPRESS UPDATE INDEXES;

-- Change storage parameters.

ALTER TABLE t1 MOVE ONLINE STORAGE (PCTINCREASE 0);

-------------xxxxxxxxxxxxx--------------------------------------

I check the state of the table index and note that it remains valid

select index_name, table_name, status from user_indexes where table_name='T1';

INDEX_NAME TABLE_NAME    STATUS
-------------------- -------------------- --------
I_MOVE1    T1             VALID


Here  I rebuild the index online in the tablespace USERS2

alter index i_move1 rebuild online tablespace users2;

I check the segments again

select segment_name, segment_type, tablespace_name from user_segments;

 SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
------------------------------ ------------------

I_MOVE1       INDEX          USERS2
T1            TABLE          USERS2

Table and index moved to the USERS2 tablespace without generating unavailability 

for the application using the new MOVE ONLINE feature of Oracle Database 12cR2

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.