oracle在线将原表修改为分区表

EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(USER, 't_audit_businessobjectinfo', DBMS_REDEFINITION.CONS_USE_PK);

create table T_AUDIT_BUSINESSOBJECTINFO_NEW
(
ID NUMBER not null,
AUDIT_PER_NAME VARCHAR2(50) not null,
AUDIT_PER_ID NUMBER not null,
AUDIT_ROLE_ID NUMBER not null,
AUDIT_DATE DATE not null,
PASSED CHAR(1) default '1' not null,
AUDIT_COMMENT VARCHAR2(512) default '同意' not null,
ADJUNCT VARCHAR2(200),
ADJUNCT_TITLE VARCHAR2(100),
SOURCE_ID VARCHAR2(200),
BUSINESS_TYPE NUMBER,
FLOWID VARCHAR2(100),
DEGREE VARCHAR2(2),
AUDITORDER NUMBER

) PARTITION BY RANGE (AUDIT_DATE)
(PARTITION P1 VALUES LESS THAN (TO_DATE('2007-1-1', 'YYYY-MM-DD')),
PARTITION P2 VALUES LESS THAN (TO_DATE('2008-1-1', 'YYYY-MM-DD')),
PARTITION P3 VALUES LESS THAN (MAXVALUE));

EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'T_AUDIT_BUSINESSOBJECTINFO', 'T_AUDIT_BUSINESSOBJECTINFO_NEW', 'ID ID, AUDIT_PER_NAME AUDIT_PER_NAME, AUDIT_PER_ID AUDIT_PER_ID, AUDIT_ROLE_ID AUDIT_ROLE_ID, AUDIT_DATE AUDIT_DATE, PASSED PASSED , AUDIT_COMMENT AUDIT_COMMENT, ADJUNCT ADJUNCT , ADJUNCT_TITLE ADJUNCT_TITLE, SOURCE_ID SOURCE_ID , BUSINESS_TYPE BUSINESS_TYPE, FLOWID FLOWID , DEGREE DEGREE , AUDITORDER AUDITORDER', DBMS_REDEFINITION.CONS_USE_PK);


drop materialized view log on T_AUDIT_BUSINESSOBJECTINFO;

EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(USER, 'T_AUDIT_BUSINESSOBJECTINFO', 'T_AUDIT_BUSINESSOBJECTINFO_NEW')
阅读更多

没有更多推荐了,返回首页