【REDEFINITION】使用在线重定义dbms_redefinition完成主键列类型的调整

发布时间：2020-08-12 00:29:28 来源：ITPUB博客阅读：221 作者：mengbing1990 栏目：建站服务器

在《【REDEFINITION】不可使用dbms_redefinition完成列类型的调整（ORA-42016）》文章中谈到，在修改主键列类型的时候因列类型不一致导致ORA-42016错误，无法完成在线重定义。

这个问题可以利用dbms_redefinition.cons_use_rowid结合字符函数（to_char）辅助完成。

解决方案如下，供参考。

1.创建表T1，包含一个NUMBER类型的主键列
sec@ora10g> create table T1 (x NUMBER(19) primary key);

Table created.

sec@ora10g> insert into t1 select rownum from all_objects;

11944 rows created.

sec@ora10g> commit;

Commit complete.

sec@ora10g> desc t1;
Name            Null?    Type
--------------- -------- ------------------
X               NOT NULL NUMBER(19)

sec@ora10g> select count(*) from t1;

COUNT(*)
----------
     11944

2.创建中间表T1，注意此时主键列的类型是VARCHAR2不是NUMBER类型
sec@ora10g> create table T2 (x varchar2(20) primary key);

Table created.

sec@ora10g> desc t2;
Name            Null?    Type
--------------- -------- ------------------
X               NOT NULL VARCHAR2(20)

sec@ora10g> select count(*) from t2;

COUNT(*)
----------
         0

3.保证在线重定义的顺利执行，授予用户所需要的权限。
sec@ora10g> conn / as sysdba
Connected.
sys@ora10g> grant EXECUTE_CATALOG_ROLE,CREATE ANY TABLE,ALTER ANY TABLE,DROP ANY TABLE,LOCK ANY TABLE,SELECT ANY TABLE to sec;

Grant succeeded.

4.使用rowid方式完成在线重定义
1）验证是否可以在线重定义
sec@ora10g> exec dbms_redefinition.can_redef_table('SEC','T1',dbms_redefinition.cons_use_rowid);

PL/SQL procedure successfully completed.

注释：此命令等同于下面的命令
sec@ora10g> exec dbms_redefinition.can_redef_table('SEC','T1',2);

2）看一下此时目标表T1和中间表T2的结构和数据
sec@ora10g> desc t1;
Name                Null?    Type
------------------- -------- ---------------------
X                   NOT NULL NUMBER(19)

sec@ora10g> desc t2
Name                Null?    Type
------------------- -------- ---------------------
X                   NOT NULL VARCHAR2(20)

sec@ora10g> select count(*) from t1;

COUNT(*)
----------
     11944

sec@ora10g> select count(*) from t2;

COUNT(*)
----------
         0

结构和数据没有变化。

3）开始在线重定义
sec@ora10g> exec dbms_redefinition.start_redef_table('SEC','T1','T2','to_char(x) x', dbms_redefinition.cons_use_rowid);

PL/SQL procedure successfully completed.

注释：此命令等同于下面的命令
sec@ora10g> exec dbms_redefinition.start_redef_table('SEC','T1','T2','to_char(x) x', 2);

关于start_redef_table参数内容的表述请参考下面内容。
PROCEDURE START_REDEF_TABLE
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
UNAME                          VARCHAR2                IN
ORIG_TABLE                     VARCHAR2                IN
INT_TABLE                      VARCHAR2                IN
COL_MAPPING                    VARCHAR2                IN     DEFAULT
OPTIONS_FLAG                   BINARY_INTEGER          IN     DEFAULT
ORDERBY_COLS                   VARCHAR2                IN     DEFAULT
PART_NAME                      VARCHAR2                IN     DEFAULT

4）看一下此时目标表T1和中间表T2的结构和数据
sec@ora10g> desc t1;
Name                Null?    Type
------------------- -------- ----------------------
X                   NOT NULL NUMBER(19)

sec@ora10g> desc t2
Name                Null?    Type
------------------- -------- ----------------------
X                   NOT NULL VARCHAR2(20)

sec@ora10g> select count(*) from t1;

COUNT(*)
----------
     11944

sec@ora10g> select count(*) from t2;

COUNT(*)
----------
     11944

此时结构没有变化，数据已经同步到中间表T2表中。

5）模拟目标表T1的事务（以删除为例）
sec@ora10g> delete from t1 where rownum<10000;

9999 rows deleted.

sec@ora10g> commit;

Commit complete.

sec@ora10g> select count(*) from t1;

COUNT(*)
----------
      1945

sec@ora10g> select count(*) from t2;

COUNT(*)
----------
     11944

此时发现T1表中数据有变化，但是中间表T2是没有变化的。很好理解，这样可以保证系统的性能。

此时我们可以使用“dbms_redefinition.finish_redef_table”完成此次在线重定义过程。也可以使用“dbms_redefinition.sync_interim_table”先同步一次数据。
sec@ora10g> exec dbms_redefinition.sync_interim_table('SEC', 'T1', 'T2');

PL/SQL procedure successfully completed.

sec@ora10g> select count(*) from t1;

COUNT(*)
----------
      1945

sec@ora10g> select count(*) from t2;

COUNT(*)
----------
      1945

可见，此时数据表T1和T2的内容又一次得到同步。

6）完成在线重定义
sec@ora10g> exec dbms_redefinition.finish_redef_table('SEC','T1','T2');

PL/SQL procedure successfully completed.

7）完成在线重定义之后我们再一次看一下目标表T1和中间表T2的结构和数据
sec@ora10g> desc t1;
Name                  Null?    Type
--------------------- -------- -------------------
X                     NOT NULL VARCHAR2(20)

sec@ora10g> desc t2;
Name                   Null?    Type
---------------------- -------- --------------------
X                      NOT NULL NUMBER(19)

sec@ora10g> select count(*) from t1;

COUNT(*)
----------
      1945

sec@ora10g> select count(*) from t2;

COUNT(*)
----------
      1945

OK，此时我们的目标已经达到，目标表T1的主键类型已经通过在线重定义方式从NUMBER类型修改成了VARCHAR2类型！
继续观察，中间表T2的X字段类型在重定义后变成了目标表的NUMBER类型。
既然重定义使命已完成，中间表T2便可以退出历史舞台，删除之。

sec@ora10g> drop table t2 purge;

Table dropped.

5.小结
在线重定义功能在保证系统高可用的前提下完成数据库调整带来了非常大的便利。
此文中描述的使用在线重定义修改主键类型的例子并不普遍，在线重定义功能主要还是集中在以下几个场景：
Online table redefinition enables you to:

    * Modify the storage parameters of a table or cluster
    * Move a table or cluster to a different tablespace in the same schema
    * Add, modify, or drop one or more columns in a table or cluster
    * Add or drop partitioning support (non-clustered tables only)
    * Change partition structure
    * Change physical properties of a single table partition, including moving it to a different tablespace in the same schema
    * Change physical properties of a materialized view log or an Oracle Streams Advanced Queueing queue table
    * Add support for parallel queries
    * Re-create a table or cluster to reduce fragmentation
    * Change the organization of a normal table (heap organized) to an index-organized table, or do the reverse.
    * Convert a relational table into a table with object columns, or do the reverse.
    * Convert an object table into a relational table or a table with object columns, or do the reverse.

参考链接：http://download.oracle.com/docs/ ... bles.htm#ADMIN01514

Good luck.

secooler
10.03.19

-- The End --

向AI问一下细节

【REDEFINITION】使用在线重定义dbms_redefinition完成主键列类型的调整

猜你喜欢

最新资讯

相关推荐

相关标签