Online Conversion of a Non-Partitioned Table to a Partitioned Table in Oracle Database 12c Release 2 (12.2)

In previous releases you could partition a non-partitioned table using EXCHANGE PARTITION or DBMS_REDEFINITION in an “almost online” manner, but both methods required multiple steps. Oracle Database 12c Release 2 makes it easier than ever to convert a non-partitioned table to a partitioned table, requiring only a single command and no downtime.

From 12.2, Alter Table <table_name> MODIFY clause can be used to convert non-partitioned table to a partitioned table.

For More details, Please see https://docs.oracle.com/en/database/oracle/oracle-database/12.2/vldbg/evolve-nopartition-table.html#GUID-5FDB7D59-DD05-40E4-8AB4-AF82EA0D0FE5

本文参考自：https://oracle-base.com/articles/12c/online-conversion-of-a-non-partitioned-table-to-a-partitioned-table-12cr2

从12.2开始，通过MODIFY TABLE可以零停机实现普通表转分区表。

一、创建测试表T1

DROP TABLE t1 PURGE;

CREATE TABLE t1 (
  id           NUMBER,
  description  VARCHAR2(50),
  created_date DATE,
  CONSTRAINT t1_pk PRIMARY KEY (id)
);

CREATE INDEX t1_created_date_idx ON t1(created_date);

INSERT INTO t1
SELECT level,
       'Description for ' || level,
       ADD_MONTHS(TO_DATE('01-JAN-2017', 'DD-MON-YYYY'), -TRUNC(DBMS_RANDOM.value(1,4)-1)*12)
FROM   dual
CONNECT BY level <= 10000;
COMMIT;

二、通过modify进行分区（1.在线分区 2.离线分区 3.在线分区+维护索引 4.二级分区）

-- Online operation.
ALTER TABLE t1 MODIFY
  PARTITION BY RANGE (created_date) (
    PARTITION t1_part_2015 VALUES LESS THAN (TO_DATE('01-JAN-2016','DD-MON-YYYY')),
    PARTITION t1_part_2016 VALUES LESS THAN (TO_DATE('01-JAN-2017','DD-MON-YYYY')),
    PARTITION t1_part_2017 VALUES LESS THAN (TO_DATE('01-JAN-2018','DD-MON-YYYY'))
  ) ONLINE;

-- Offline operation.
ALTER TABLE t1 MODIFY
  PARTITION BY RANGE (created_date) (
    PARTITION t1_part_2015 VALUES LESS THAN (TO_DATE('01-JAN-2016','DD-MON-YYYY')),
    PARTITION t1_part_2016 VALUES LESS THAN (TO_DATE('01-JAN-2017','DD-MON-YYYY')),
    PARTITION t1_part_2017 VALUES LESS THAN (TO_DATE('01-JAN-2018','DD-MON-YYYY'))
  );

-- Online operation with modification of index partitioning.
ALTER TABLE t1 MODIFY
  PARTITION BY RANGE (created_date) (
    PARTITION t1_part_2015 VALUES LESS THAN (TO_DATE('01-JAN-2016','DD-MON-YYYY')),
    PARTITION t1_part_2016 VALUES LESS THAN (TO_DATE('01-JAN-2017','DD-MON-YYYY')),
    PARTITION t1_part_2017 VALUES LESS THAN (TO_DATE('01-JAN-2018','DD-MON-YYYY'))
  ) ONLINE
  UPDATE INDEXES
  (
    t1_pk GLOBAL,
    t1_created_date_idx LOCAL
  );

--Composite Partition (Sub-Partition) a Table
ALTER TABLE t1 MODIFY
  PARTITION BY RANGE (created_date) SUBPARTITION BY HASH (id)(
    PARTITION t1_part_2015 VALUES LESS THAN (TO_DATE('01-JAN-2016','DD-MON-YYYY')) (
      SUBPARTITION t1_sub_part_2015_1,
      SUBPARTITION t1_sub_part_2015_2,
      SUBPARTITION t1_sub_part_2015_3,
      SUBPARTITION t1_sub_part_2015_4
    ),
    PARTITION t1_part_2016 VALUES LESS THAN (TO_DATE('01-JAN-2017','DD-MON-YYYY')) (
      SUBPARTITION t1_sub_part_2016_1,
      SUBPARTITION t1_sub_part_2016_2,
      SUBPARTITION t1_sub_part_2016_3,
      SUBPARTITION t1_sub_part_2016_4
    ),
    PARTITION t1_part_2017 VALUES LESS THAN (TO_DATE('01-JAN-2018','DD-MON-YYYY')) (
      SUBPARTITION t1_sub_part_2017_1,
      SUBPARTITION t1_sub_part_2017_2,
      SUBPARTITION t1_sub_part_2017_3,
      SUBPARTITION t1_sub_part_2017_4
    )
  ) ONLINE
  UPDATE INDEXES
  (
    t1_pk GLOBAL,
    t1_created_date_idx LOCAL
  );

三、分区后检查

-- Check indexes.
SELECT index_name, partitioned, status
FROM   user_indexes
ORDER BY 1;

INDEX_NAME           PARTITIONED STATUS
-------------------- ----------- --------
T1_CREATED_DATE_IDX  YES         N/A
T1_PK                NO          VALID

SQL>

-- Check index partitions.
SELECT index_name, partition_name, status
FROM   user_ind_partitions
ORDER BY 1,2;

INDEX_NAME           PARTITION_NAME       STATUS
-------------------- -------------------- --------
T1_CREATED_DATE_IDX  T1_PART_2015         USABLE
T1_CREATED_DATE_IDX  T1_PART_2016         USABLE
T1_CREATED_DATE_IDX  T1_PART_2017         USABLE

SQL>

四、限制条件

Restrictions

There are some restrictions associated with this functionality.

• It can’t be used to partition an index-organized table (IOT).
• It can’t be used if the table has a domain index.
• You can only convert a table to a reference-partitioned child table in offline mode.