在《【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.

參考連結： ... bles.htm#ADMIN01514

Good luck.

secooler
10.03.19

-- The End --

【REDEFINITION】使用線上重定義dbms_redefinition完成主鍵列型別的調整

相關文章