在线重定义分区表
--赋权限
--调用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;
--调用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;
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博客 ” ,链接:http://blog.itpub.net/25323853/viewspace-708913/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25323853/viewspace-708913/