8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 12c » Here

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.

Related articles.

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.

MOVE ONLINE (12.2)

Oracle Database 12c Release 2 (12.2) allows regular tables to be moved online, as described here.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.