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可以零停机实现普通表转分区表。
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;
-- 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>
There are some restrictions associated with this functionality.