Saturday, 1 October 2016

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

In Oracle 12c it is now possible to move table partitions and sub-partitions as online operations.

  • 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.
  1. It cannot be used for tables owned by SYS, IOTs, tables with object types, or tables with bitmap join or domain indexes.
  2. 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.
  3. Parallel DML and direct path inserts are not supported against an object with an ongoing online move.