11G DBMS_REDEFINITION修改表数据类型

1.获取源表结构信息

SYS@honor1>SETLONG999999SYS@honor1>SELECTDBMS_METADATA.GET_DDL('TABLE','TEST_REDE','HR')FROMDUAL;CREATETABLE"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.创建中间表，修改需要修改的列数据类型

CREATETABLE"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.开始在线重定义

altersessionforceparalleldmlparallel4;altersessionforceparallelqueryparallel4;BEGINDBMS_REDEFINITION.START_REDEF_TABLE(uname=>'HR',orig_table=>'TEST_REDE',int_table=>'TEST_REDE_INTER',col_mapping=>'OWNEROWNER,OBJECT_NAMEOBJECT_NAME,SUBOBJECT_NAMESUBOBJECT_NAME,OBJECT_IDOBJECT_ID,to_char(DATA_OBJECT_ID)DATA_OBJECT_ID,--由于更改数据类型，需要手工转换OBJECT_TYPEOBJECT_TYPE,CREATEDCREATED,LAST_DDL_TIMELAST_DDL_TIME,TIMESTAMPTIMESTAMP,STATUSSTATUS,TEMPORARYTEMPORARY,GENERATEDGENERATED,SECONDARYSECONDARY,NAMESPACENAMESPACE,EDITION_NAMEEDITION_NAME',options_flag=>DBMS_REDEFINITION.CONS_USE_ROWID);END;/5.同步中间数据，减少finish_redef_table时间

begindbms_redefinition.sync_interim_table(uname=>'&USERNAME',orig_table=>'&SOURCE_TAB',int_table=>'&INT_TAB');end;/6.复制相关约束，依赖对象

#如果更改了列名，或者增加列，必要时，手工创建相关索引等对象DECLAREnum_errorsPLS_INTEGER;BEGINDBMS_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