ITPub博客

首页 > 数据库 > Oracle > Oracle 11G DBMS_REDEFINITION修改表数据类型

Oracle 11G DBMS_REDEFINITION修改表数据类型

原创 Oracle 作者:你好我是李白 时间:2020-07-12 15:12:06 0 删除 编辑

11G DBMS_REDEFINITION修改表数据类型

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.完成在线重定义

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

参考:

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


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31439444/viewspace-2703986/,如需转载,请注明出处,否则将追究法律责任。

下一篇: V8 Bundled Exec call
全部评论
让生活更美好。

注册时间:2017-02-28

  • 博文量
    80
  • 访问量
    39966