把普通表改为Partition表-优快云博客

本文详细介绍了如何使用DBMS_REDEFINITION包在Oracle数据库中进行表重构和分区操作，包括创建新表、切换表定义及同步临时表，同时展示了分区表的创建和查询方法。

EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('QA2_ATGCORE_MF', 'ngp_user_site', DBMS_REDEFINITION.CONS_USE_PK);

CREATE TABLE QA2_ATGCORE_MF.NGP_USER_SITE_NEW
(
USER_ID VARCHAR2(40 BYTE) NOT NULL,
SITE_ID VARCHAR2(40 BYTE) NOT NULL
)
PARTITION BY LIST (SITE_ID)
(
PARTITION S1 VALUES('1'),
PARTITION S2 VALUES('2'),
PARTITION S3 VALUES('3'),
PARTITION S4 VALUES('4'),
PARTITION S5 VALUES('5'),
PARTITION S6 VALUES('6'),
PARTITION S7 VALUES('7'),
PARTITION S8 VALUES('8'),
PARTITION rest VALUES (DEFAULT)
);

EXEC DBMS_REDEFINITION.START_REDEF_TABLE('QA2_ATGCORE_MF', 'ngp_user_site', 'NGP_USER_SITE_NEW');
EXEC DBMS_REDEFINITION.sync_interim_table('QA2_ATGCORE_MF', 'ngp_user_site', 'NGP_USER_SITE_NEW');
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('QA2_ATGCORE_MF', 'ngp_user_site', 'NGP_USER_SITE_NEW');
SELECT * FROM DBA_TAB_PARTITIONS where table_owner='QA2_ATGCORE_MF' AND TABLE_NAME='NGP_USER_SITE';
SELECT COUNT(*) FROM QA2_ATGCORE_MF.ngp_user_site;
SELECT COUNT(*) FROM QA2_ATGCORE_MF.ngp_user_site PARTITION (S1);
SELECT COUNT(*) FROM QA2_ATGCORE_MF.ngp_user_site PARTITION (S7);
SELECT COUNT(*) FROM QA2_ATGCORE_MF.ngp_user_site PARTITION (S8);