8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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.
- All Partitioning Articles
- Online Move of a Table in Oracle Database 12c Release 2 (12.2)
- Partitioning Enhancements in Oracle Database 12c Release 1 (12.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.
- This functionality is not available for materialized views.
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:
- All Partitioning Articles
- Online Move of a Table in Oracle Database 12c Release 2 (12.2)
- Partitioning Enhancements in Oracle Database 12c Release 1 (12.1)
Hope this helps. Regards Tim...