重庆分公司
重庆分公司，新征程启航
为企业提供网站建设、域名注册、服务器等服务
【REDEFINITION】使用在线重定义dbms_redefinition完成主键列类型的调整
在《【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 --
当前文章：【REDEFINITION】使用在线重定义dbms_redefinition完成主键列类型的调整
分享链接：http://cqcxhl.com/article/iepedp.html
这个问题可以利用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 --
当前文章：【REDEFINITION】使用在线重定义dbms_redefinition完成主键列类型的调整
分享链接：http://cqcxhl.com/article/iepedp.html