溫馨提示×

溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊×
其他方式登錄
點(diǎn)擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》

【REDEFINITION】使用在線重定義dbms_redefinition完成主鍵列類型的調(diào)整

發(fā)布時間：2020-08-12 00:29:28 來源：ITPUB博客 閱讀：210 作者：mengbing1990 欄目：建站服務(wù)器
在《【REDEFINITION】不可使用dbms_redefinition完成列類型的調(diào)整（ORA-42016）》文章中談到，在修改主鍵列類型的時候因列類型不一致導(dǎo)致ORA-42016錯誤，無法完成在線重定義。

這個問題可以利用dbms_redefinition.cons_use_rowid結(jié)合字符函數(shù)（to_char）輔助完成。

解決方案如下，供參考。

1.創(chuàng)建表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.創(chuàng)建中間表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.保證在線重定義的順利執(zhí)行，授予用戶所需要的權(quán)限。
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）看一下此時目標(biāo)表T1和中間表T2的結(jié)構(gòu)和數(shù)據(jù)
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

結(jié)構(gòu)和數(shù)據(jù)沒有變化。

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);

關(guān)于start_redef_table參數(shù)內(nèi)容的表述請參考下面內(nèi)容。
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）看一下此時目標(biāo)表T1和中間表T2的結(jié)構(gòu)和數(shù)據(jù)
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

此時結(jié)構(gòu)沒有變化，數(shù)據(jù)已經(jīng)同步到中間表T2表中。

5）模擬目標(biāo)表T1的事務(wù)（以刪除為例）
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

此時發(fā)現(xiàn)T1表中數(shù)據(jù)有變化，但是中間表T2是沒有變化的。很好理解，這樣可以保證系統(tǒng)的性能。

此時我們可以使用“dbms_redefinition.finish_redef_table”完成此次在線重定義過程。也可以使用“dbms_redefinition.sync_interim_table”先同步一次數(shù)據(jù)。
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

可見，此時數(shù)據(jù)表T1和T2的內(nèi)容又一次得到同步。

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

PL/SQL procedure successfully completed.

7）完成在線重定義之后我們再一次看一下目標(biāo)表T1和中間表T2的結(jié)構(gòu)和數(shù)據(jù)
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，此時我們的目標(biāo)已經(jīng)達(dá)到，目標(biāo)表T1的主鍵類型已經(jīng)通過在線重定義方式從NUMBER類型修改成了VARCHAR2類型！
繼續(xù)觀察，中間表T2的X字段類型在重定義后變成了目標(biāo)表的NUMBER類型。
既然重定義使命已完成，中間表T2便可以退出歷史舞臺，刪除之。

sec@ora10g> drop table t2 purge;

Table dropped.

5.小結(jié)
在線重定義功能在保證系統(tǒng)高可用的前提下完成數(shù)據(jù)庫調(diào)整帶來了非常大的便利。
此文中描述的使用在線重定義修改主鍵類型的例子并不普遍，在線重定義功能主要還是集中在以下幾個場景：
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問一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手