Move Table Online – 12cR2

Until 11gR2 to move a table to different tablespace there were two available methods, first one was ALTER TABLE .. MOVE TABLESPACE but during this the table was locked for DML, thus downtime and the second one was DBMS_REDEFINITION which will allow to create a new table on the desired tablespace and sync the data between them ONLINE and when the sync ends the tables names will be switched BUT this involved lot of manually work.

With 12cR1 you can Move Table Partition/Sub-Partition Online but with 12cR2 you can even move a table online.

Now lets demonstrate this online move:-

Step-1: Create Table:-

CREATE TABLE t2
(id            NUMBER,
 name   VARCHAR2(50),
 created_date  DATE,
 CONSTRAINT t1_pk PRIMARY KEY (id)
);

Step-2: Insert Records:-

INSERT INTO t2
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;

Step-3:- Move Table:-

ALTER TABLE T2 MOVE ONLINE TABLESPACE <code>USERS</code><code class="plain plain">UPDATE INDEXES;</code>; 

There are few restrictions on Moving Table Partitions:-

  • You cannot combine this clause with any other clause in the same statement.
  • You cannot specify this clause for a partitioned index-organized table.
  • You cannot specify this clause if a domain index is defined on the table.
  • Parallel DML and direct path INSERT operations require an exclusive lock on the table. Therefore, these operations are not supported concurrently with an ongoing online table MOVE, due to conflicting locks.
  • You cannot specify this clause for index-organized tables that contain any LOB, VARRAY, Oracle-supplied type, or user-defined object type columns.

Hope this helps, in case of any questions or assistance please comment below and we’ll try to assist you.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.