Oracle 11G DBMS_REDEFINITION修改表数据类型

11G DBMS_REDEFINITION修改表数据类型

1.获取源表结构信息

SYS@honor1 > SET LONG 999999SYS@honor1 > SELECT DBMS_METADATA.GET_DDL('TABLE','TEST_REDE','HR') FROM DUAL;CREATE TABLE "HR"."TEST_REDE"  (    "OWNER" VARCHAR2(30),       "OBJECT_NAME" VARCHAR2(128),       "SUBOBJECT_NAME" VARCHAR2(30),       "OBJECT_ID" VARCHAR2(20),       "DATA_OBJECT_ID" NUMBER,       "OBJECT_TYPE" VARCHAR2(19),       "CREATED" DATE,       "LAST_DDL_TIME" DATE,       "TIMESTAMP" VARCHAR2(19),       "STATUS" VARCHAR2(7),       "TEMPORARY" VARCHAR2(1),       "GENERATED" VARCHAR2(1),       "SECONDARY" VARCHAR2(1),       "NAMESPACE" NUMBER,       "EDITION_NAME" VARCHAR2(30)  )  TABLESPACE "USERS";

2.验证是否可以在线重定义

# 由于表没有主键，所以只能使用ROWID，如果表有主键，可以删掉CONS_USE_ROWID，默认使用PKBEGINDBMS_REDEFINITION.CAN_REDEF_TABLE('HR','TEST_REDE',DBMS_REDEFINITION.CONS_USE_ROWID);END;/

3.创建中间表，修改需要修改的列数据类型

CREATE TABLE "HR"."TEST_REDE_INTER"(    "OWNER" VARCHAR2(30),     "OBJECT_NAME" VARCHAR2(128),     "SUBOBJECT_NAME" VARCHAR2(30),     "OBJECT_ID" VARCHAR2(20),     "DATA_OBJECT_ID" VARCHAR2(10),         --需要修改后的数据类型     "OBJECT_TYPE" VARCHAR2(19),     "CREATED" DATE,     "LAST_DDL_TIME" DATE,     "TIMESTAMP" VARCHAR2(19),     "STATUS" VARCHAR2(7),     "TEMPORARY" VARCHAR2(1),     "GENERATED" VARCHAR2(1),     "SECONDARY" VARCHAR2(1),     "NAMESPACE" NUMBER,     "EDITION_NAME" VARCHAR2(30))TABLESPACE "USERS";

4.开始在线重定义

alter session force parallel dml parallel 4;alter session force parallel query parallel 4;BEGINDBMS_REDEFINITION.START_REDEF_TABLE(uname => 'HR',orig_table => 'TEST_REDE',int_table => 'TEST_REDE_INTER',col_mapping => 'OWNER OWNER,       OBJECT_NAME OBJECT_NAME,SUBOBJECT_NAME SUBOBJECT_NAME,OBJECT_ID OBJECT_ID,to_char(DATA_OBJECT_ID) DATA_OBJECT_ID,    --由于更改数据类型，需要手工转换OBJECT_TYPE OBJECT_TYPE,CREATED CREATED,LAST_DDL_TIME LAST_DDL_TIME,TIMESTAMP TIMESTAMP,STATUS STATUS,TEMPORARY TEMPORARY,GENERATED GENERATED,SECONDARY SECONDARY,NAMESPACE NAMESPACE,EDITION_NAME EDITION_NAME',options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);END;/

5.同步中间数据，减少finish_redef_table时间

begin    dbms_redefinition.sync_interim_table(uname        => '&USERNAME',                                           orig_table => '&SOURCE_TAB',                                           int_table  => '&INT_TAB');end;/

6.复制相关约束，依赖对象

# 如果更改了列名，或者增加列，必要时，手工创建相关索引等对象DECLARE    num_errors PLS_INTEGER;BEGIN    DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname            => '&USERNAME',                                            orig_table       => '&SOURCE_TAB',                                            int_table        => '&INT_TAB',                                            copy_indexes     => DBMS_REDEFINITION.cons_orig_params,                                            copy_triggers    => TRUE,                                            copy_constraints => TRUE,                                            copy_privileges  => TRUE,                                            ignore_errors    => FALSE,                                            num_errors       => num_errors,                                            copy_statistics  => TRUE);END;/

7.完成在线重定义

begindbms_redefinition.finish_redef_table(uname      => '&USERNAME',                                     orig_table => '&SOURCE_TAB',                                     int_table  => '&INT_TAB');end;

参考：

https://blog.csdn.net/bikeorcl/article/details/103974032