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.