在线重定义分区表

--赋权限
--调用DBMS_REDEFINITION包需要EXECUTE_CATALOG_ROLE角色，除此之外，
--还需要CREATE ANY TABLE、ALTER ANY TABLE、DROP ANY TABLE、LOCK ANY TABLE和SELECT ANY TABLE的权限。
GRANT CREATE ANY TABLE,ALTER ANY TABLE,DROP ANY TABLE,LOCK ANY TABLE,SELECT ANY TABLE TO JASON;
GRANT EXECUTE ON DBMS_REDEFINITION TO JASON ;
/
DROP TABLE TEST
/
CREATE TABLE TEST (ID NUMBER PRIMARY KEY, TIME DATE);
/
INSERT INTO TEST SELECT ROWNUM, CREATED FROM DBA_OBJECTS;
/
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE(USER,'TEST',DBMS_REDEFINITION.CONS_USE_PK);
END;
/
CREATE TABLE TEST_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME)
(PARTITION P1 VALUES LESS THAN (TO_DATE('2011-7-1', 'YYYY-MM-DD')),
PARTITION P2 VALUES LESS THAN (TO_DATE('2011-8-1', 'YYYY-MM-DD')),
PARTITION P3 VALUES LESS THAN (TO_DATE('2011-9-1', 'YYYY-MM-DD')),
PARTITION P4 VALUES LESS THAN (MAXVALUE));
/
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(USER,'TEST','TEST_NEW');
END;

/
BEGIN
   DBMS_REDEFINITION.SYNC_INTERIM_TABLE(USER, 'TEST', 'TEST_NEW');
END;
--现在，将中间表与原始表同步。（仅当要对表 TEST 进行更新时才需要执行该操作。）
/
BEGIN
   DBMS_REDEFINITION.FINISH_REDEF_TABLE(USER, 'TEST', 'TEST_NEW');
END;
/
BEGIN
   DBMS_REDEFINITION.CAN_REDEF_TABLE(USER,'TEST',DBMS_REDEFINITION.CONS_USE_PK);
END;

ITPub博客

Redefining Tables Online（二）

最新文章