Online Move of Partitions and Sub-Partitions in Oracle 12c Release 1

MOVE PARTITION ... ONLINE

The following example shows how to online move a table partition.

-- Create partitioned table.

DROP TABLE t1 PURGE;

CREATE TABLE t1

(id            NUMBER,

 description   VARCHAR2(50),

 created_date  DATE)

PARTITION BY RANGE (created_date)

(PARTITION part_2014 VALUES LESS THAN (TO_DATE('01/01/2015', 'DD/MM/YYYY')) TABLESPACE users,

 PARTITION part_2015 VALUES LESS THAN (TO_DATE('01/01/2016', 'DD/MM/YYYY')) TABLESPACE users);

-- Populate it.

INSERT INTO t1

SELECT level,

       'Description for ' || level,

       CASE

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

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

       END

FROM   dual

CONNECT BY level <= 1000;

COMMIT;

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

-- Check partitions.

COLUMN table_name FORMAT A20

COLUMN partition_name FORMAT A20

SELECT table_name,

       partition_name,

       num_rows

FROM   user_tab_partitions

ORDER BY 1,2;

TABLE_NAME           PARTITION_NAME         NUM_ROWS

-------------------- -------------------- ----------

T1                   PART_2014                   500

T1                   PART_2015                   500

SQL>

We can now move a partition using the ONLINE keyword.

ALTER TABLE t1 MOVE PARTITION part_2015 ONLINE TABLESPACE users UPDATE INDEXES;

Table altered.

SQL>

MOVE SUBPARTITION ... ONLINE

The following example shows how to online move a table sub-partition.

-- Create sub-partitioned table.

DROP TABLE t1 PURGE;

CREATE TABLE t1

(id            NUMBER,

 description   VARCHAR2(50),

 created_date  DATE)

PARTITION BY RANGE (created_date)

SUBPARTITION BY HASH (id)

SUBPARTITIONS 4

(PARTITION part_2014 VALUES LESS THAN (TO_DATE('01/01/2015', 'DD/MM/YYYY')) TABLESPACE users,

 PARTITION part_2015 VALUES LESS THAN (TO_DATE('01/01/2016', 'DD/MM/YYYY')) TABLESPACE users);

-- Populate it.

INSERT INTO t1

SELECT level,

       'Description for ' || level,

       CASE

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

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

       END

FROM   dual

CONNECT BY level <= 1000;

COMMIT;

EXEC DBMS_STATS.gather_table_stats(USER, 't1', granularity => 'SUBPARTITION');

-- Check sub-partitions.

COLUMN table_name FORMAT A20

COLUMN partition_name FORMAT A20

COLUMN subpartition_name FORMAT A20

SELECT table_name,

       partition_name,

       subpartition_name,

       num_rows

FROM   user_tab_subpartitions

ORDER BY 1,2,3;

TABLE_NAME           PARTITION_NAME       SUBPARTITION_NAME      NUM_ROWS

-------------------- -------------------- -------------------- ----------

T1                   PART_2014            SYS_SUBP786                 214

T1                   PART_2014            SYS_SUBP787                 272

T1                   PART_2014            SYS_SUBP788                 242

T1                   PART_2014            SYS_SUBP789                 272

T1                   PART_2015            SYS_SUBP790                 254

T1                   PART_2015            SYS_SUBP791                 216

T1                   PART_2015            SYS_SUBP792                 280

T1                   PART_2015            SYS_SUBP793                 250

SQL>

We can now move a sub-partition using the ONLINE keyword.

ALTER TABLE t1 MOVE SUBPARTITION SYS_SUBP793 ONLINE TABLESPACE users UPDATE INDEXES;

Table altered.

SQL>

Restrictions

There are some restrictions associated with the ONLINE clause.

• It cannot be used for tables owned by SYS, IOTs, tables with object types, or tables with bitmap join or domain indexes.
• It cannot be used in 12.1.0.1 when database-level supplemental logging is enabled. From 12.1.0.2 this restriction is lifted.
• Parallel DML and direct path inserts are not supported against an object with an ongoing online move.