12c新機能 1ステップでの表のオンライン再定義

オンライン再定義はオブジェクトの定義変更をオンラインで実行できる Oracle Database Enterprise Editionで利用可能な機能です。非常に強力な機能ですが、使用手順が若干面倒なのがちょっとした難点でした。（慣れればそれほどでもないのですが）

従来のバージョンでのオンライン再定義の手順概略は以下の通りです。

DBMS_REDEFINITION.CAN_REDEF_TABLEプロシージャを起動して、表をオンラインで再定義できることを確認
今回の再定義で変更したい内容を表（仮表）を作成する。
たとえば、今回のオンライン再定義で表の格納先表領域を移動したい場合は、移動先の表領域に同じ定義の表を作成する。
DBMS_REDEFINITION.START_REDEF_TABLE でオンライン再定義を開始する。
このタイミングでの元表のデータが仮表にコピーされる。
索引や制約などの依存オブジェクトと統計を元の表から仮表へコピーする。
DBMS_REDEFINITIONFINISH_REDEF_TABLE でオンライン再定義を終了する。
DBMS_REDEFINITION.START_REDEF_TABLE 実行後に元表に対して加えられた変更が、仮表に反映される。

12cでは、このような若干面倒な手順が、DBMS_REDEFINITION.REDEF_TABLEを実行するだけに単純化されます。

例：従来のオンライン再定義の手順で格納先表領域を変更する

以下の手順は MOS Doc:1357742.1 を参考にしています。まず、格納先表領域を変更したい表ORIGINALを作成し、データを投入しておきます。

SQL> create table ORIGINAL (
  2    COL1 NUMBER PRIMARY KEY,
  3    COL2 VARCHAR2(1000))
  4    TABLESPACE SYSTEM;

表が作成されました。

SQL> insert into ORIGINAL values(1,'AAA');
1行が作成されました。

SQL> commit;

コミットが完了しました。

次に仮表を作成します。ここでは、（誤って）SYSTEM表領域に作成した表をUSERS表領域に移動ししたい状況を想定します。ですので、仮表はUSERS表領域に作成します。列定義は同様とします。

SQL> create table INTERIM (
  2    COL1 NUMBER,
  3    COL2 VARCHAR2(1000))
  4    TABLESPACE USERS;

表が作成されました。

SQL> col table_name format a16
SQL> set lines 80
SQL> SELECT TABLE_NAME, TABLESPACE_NAME FROM USER_TABLES;

TABLE_NAME       TABLESPACE_NAME
---------------- ------------------------------
INTERIM          USERS
ORIGINAL         SYSTEM

DBMS_REDEFINITION.CAN_REDEF_TABLEでオンライン再定義が可能か確認し（エラーがなければOK）、DBMS_REDEFINITION.START_REDEF_TABLEでオンライン再定義を開始します。

SQL> SET SERVEROUTPUT ON

SQL> exec DBMS_REDEFINITION.CAN_REDEF_TABLE('RYWATABE','ORIGINAL', DBMS_REDEFINITION.CONS_USE_PK);
PL/SQLプロシージャが正常に完了しました。

SQL> BEGIN
  2     DBMS_REDEFINITION.START_REDEF_TABLE(
  3                    uname => 'RYWATABE',
  4                    orig_table => 'ORIGINAL',
  5                    int_table => 'INTERIM',
  6                    options_flag => DBMS_REDEFINITION.CONS_USE_PK);
  7  END;
  8  /

PL/SQLプロシージャが正常に完了しました。

仮表には、索引や制約が設定されていないので、DBMS_REDEFINITION.COPY_TABLE_DEPENDENTSで、元表からコピーします。


SQL> DECLARE
  2     error_count pls_integer := 0;
  3  BEGIN
  4     DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('RYWATABE', 'ORIGINAL', 'INTERIM',
  5        dbms_redefinition.cons_orig_params, TRUE,TRUE,TRUE,FALSE, error_count);
  6     DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count));
END;
  8  /
errors := 0

PL/SQLプロシージャが正常に完了しました。

現段階では、オンライン再定義は実行中の状態となりますが、この状態で、元表に対してDMLを実行できます。元表に加えた更新は、この時点では仮表に反映されません。

SQL> col col2 format a16
SQL>
SQL> SELECT * FROM ORIGINAL;

      COL1 COL2
---------- ----------------
         1 AAA

SQL> SELECT * FROM INTERIM;

      COL1 COL2
---------- ----------------
         1 AAA

SQL> insert into ORIGINAL values(2,'BBB');

1行が作成されました。

SQL> commit;

コミットが完了しました。

SQL> SELECT * FROM ORIGINAL;

      COL1 COL2
---------- ----------------
         1 AAA
         2 BBB

SQL> SELECT * FROM INTERIM;

      COL1 COL2
---------- ----------------
         1 AAA

DBMS_REDEFINITION.FINISH_REDEF_TABLEを実行して、オンライン再定義を終了します。この時点で、元表に加えられた変更が仮表に反映されます。

SQL> exec DBMS_REDEFINITION.FINISH_REDEF_TABLE('RYWATABE','ORIGINAL','INTERIM');

PL/SQLプロシージャが正常に完了しました。

SQL> SELECT * FROM ORIGINAL;

      COL1 COL2
---------- ----------------
         1 AAA
         2 BBB

SQL> SELECT * FROM INTERIM;

      COL1 COL2
---------- ----------------
         1 AAA
         2 BBB

例：1ステップでの表のオンライン再定義の手順で格納先表領域を変更する(DBMS_REDEFINITION.REDEF_TABLE)

次に、Oracle Database 12c (12.1.0.1)で導入された、1ステップでの表のオンライン再定義で同様のことをやってみます。まず、テーブルとデータを準備します。仮表を準備する必要はありません。

SQL> create table ORIGINAL (
  2    COL1 NUMBER PRIMARY KEY,
  3    COL2 VARCHAR2(1000))
  4  TABLESPACE SYSTEM;

表が作成されました。

SQL> insert into ORIGINAL values(1,'AAA');

1行が作成されました。

SQL> commit;

コミットが完了しました。

SQL> SELECT TABLE_NAME, TABLESPACE_NAME FROM USER_TABLES;

TABLE_NAME       TABLESPACE_NAME
---------------- ------------------------------
ORIGINAL         SYSTEM

DBMS_REDEFINITION.REDEF_TABLEを実行します。ここで実行したい定義変更は表領域の移動なので、引数table_part_tablespaceに'USERS'を指定しています。指定する引数は実行したい定義変更により異なります。詳細はマニュアルをご確認ください。

SQL> BEGIN
  2    DBMS_REDEFINITION.REDEF_TABLE(
  3      uname                      => 'RYWATABE',
  4      tname                      => 'ORIGINAL',
  5      table_part_tablespace      => 'USERS'
  6      );
  7  END;
  8  /

PL/SQLプロシージャが正常に完了しました。

SQL> SELECT TABLE_NAME, TABLESPACE_NAME FROM USER_TABLES;

TABLE_NAME       TABLESPACE_NAME
---------------- ------------------------------
ORIGINAL         USERS