准备工作
提取表和索引的语句。
SELECT DBMS_METADATA.GET_DDL(OBJECT_TYPE => 'TABLE', NAME => 'CONTACT_CLSLIST') || DBMS_METADATA.GET_DEPENDENT_DDL(OBJECT_TYPE => 'INDEX', BASE_OBJECT_NAME => 'CONTACT_CLSLIST') FROM DUAL ;
根据生产表的结构修改中间表
CREATE TABLE "INCRCBPS8"."CONTACT_CLSLIST_TEST"
( "CUST_ID" NUMBER(38,0),
"CONTACT_SEQ" NUMBER(38,0),
"CONTACT_CLASS_SEQ" NUMBER(38,0),
"CONTACT_CLASS_CODE" CHAR(1),
"CONTACT_NO" VARCHAR2(680),
"CONTACT_EXT" VARCHAR2(680),
"FROM_BRANCH" VARCHAR2(30),
"TRANS_ID" VARCHAR2(60),
"COMMIT_TIMESTAMP" VARCHAR2(20),
"COMMIT_CSN" NUMBER,
"OP_SEQ" NUMBER,
"LAST_OP_FLAG" VARCHAR2(20),
"LAST_TIME" DATE DEFAULT SYSDATE,
"HAVE_FLAG" VARCHAR2(50) DEFAULT '0',
"HAVE_FLAG_1" VARCHAR2(50) DEFAULT '0',
"HAVE_FLAG_2" VARCHAR2(50) DEFAULT '0',
"HAVE_FLAG_3" VARCHAR2(50) DEFAULT '0',
"HAVE_FLAG_4" VARCHAR2(50) DEFAULT '0'
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOLOGGING
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "CMDS"
PARTITION BY RANGE ("LAST_TIME") INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
SUBPARTITION BY LIST ("HAVE_FLAG")
SUBPARTITION TEMPLATE (
SUBPARTITION "SUBP0" VALUES ( '0' ),
SUBPARTITION "SUBOTHER" VALUES ( default ) )
(PARTITION "P1" VALUES LESS THAN (TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD
HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "CMDS" NOLOGGING )
中间表创建完毕之后，创建相关索引
将步骤1的SQL中的表名CONTACT_CLSLIST替换为CONTACT_CLSLIST_TEST创建中间表
中间表新增字段remark01
alter table CONTACT_CLSLIST_TEST add remark01 varchar2(10) default 'A';
检查能否进行重定义,过程执行成功即说明可以重定义，因为测试表没有主键，所以要写上用rowid，如果有主键，就是CONS_USE_PK
begin
DBMS_REDEFINITION.CAN_REDEF_TABLE('cmds','CONTACT_CLSLIST',DBMS_REDEFINITION.CONS_USE_ROWID);
end;
/
如果有主键，就是CONS_USE_PK
begin
DBMS_REDEFINITION.CAN_REDEF_TABLE('cmds','CONTACT_CLSLIST',DBMS_REDEFINITION.CONS_USE_PK);
end;
/
开始重定义表
运行start_redef_table过程
BEGIN
dbms_redefinition.start_redef_table(uname => CMDS,orig_table => 'CONTACT_CLSLIST', int_table => 'CONTACT_CLSLIST_TEST', options_flag => DBMS_REDEFINITION.cons_use_rowid);
END;
/
开始同步中间表
BEGIN
dbms_redefinition.sync_interim_table(uname => 'CMDS',orig_table => 'CONTACT_CLSLIST',int_table => 'CONTACT_CLSLIST_TEST');
END;
/
完成同步
BEGIN
dbms_redefinition.finish_redef_table(uname => 'CMDS',orig_table => 'CONTACT_CLSLIST',int_table => 'CONTACT_CLSLIST_TEST');
END;
/
删除中间表
drop table CONTACT_CLSLIST_TEST;
修改索引名称
alter index "IDX_CONTACT_CLSLIST_NO_mid" rename to IDX_CONTACT_CLSLIST_NO;
alter index "IDX_CONTACT_CLSLIST_INDEX_mid" rename to IDX_CONTACT_CLSLIST_INDEX;
执行完以上的9个步骤，新增字段就创建成功了。