oracle 表重命名，oracle 11g 通過在線重定義方式修改表結構

今天因為要對一套數據庫的數據抽取進行io優化，希望通過修改表結構將抽取io降下來，因為抽取只針對標簽HAVE_FLAG為“0”的值進行抽取，抽取之后更新HAVE_FLAG為其他值，所以計劃將HAVE_FLAG在時間分區里面再按照“0”和其他值進行list分區，控制SQL掃描的范圍，再配合索引，降低IO。

準備工作

提取表和索引的語句。

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  NOLOGGINGSTORAGE(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-DDHH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255STORAGE(BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "CMDS"  NOLOGGING )

中間表創建完畢之后，創建相關索引

將步驟1的SQL中的表名CONTACT_CLSLIST替換為CONTACT_CLSLIST_TEST創建中間表

oracle 表重命名？中間表新增字段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;
/

oracle數據庫定義變量和使用？刪除中間表

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個步驟，新增字段就創建成功了。