当前位置 博文首页 > LuciferLiu_DBA：Oracle分区表之MODIFY clause added to the ALT
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.
?
?
?
