Oracle11GDBMS_REDEFINITION修改表数据类型
11G DBMS_REDEFINITION修改表数据类型
创新互联公司-专业网站定制、快速模板网站建设、高性价比平城网站开发、企业建站全套包干低至880元,成熟完善的模板库,直接使用。一站式平城网站制作公司更省心,省钱,快速模板网站建设找我们,业务覆盖平城地区。费用合理售后完善,10多年实体公司更值得信赖。
1.获取源表结构信息
SYS@honor1 > SET LONG 999999 SYS@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,默认使用PK BEGIN DBMS_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; BEGIN DBMS_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.完成在线重定义
参考:
https://blog.csdn.net/bikeorcl/article/details/103974032
当前题目:Oracle11GDBMS_REDEFINITION修改表数据类型
当前地址:http://myzitong.com/article/ggdjsd.html