DBMS_REDEFINITIONパッケージによるOracleオンライン表パーティション化の完全ガイド

Oracle® 10g以降では、DBMS_REDEFINITIONパッケージを利用することで、アプリケーションを一切停止させることなく(ダウンタイムゼロで)表のパーティション化をオンラインで実行できます。

本記事では、DBMS_REDEFINITIONを使用して非パーティション表をパーティション表へ変更する具体的な手順を解説します。例として、非パーティション表TABLEAをレンジ・インターバル・パーティション表に変換するケースを取り上げます。

ステップ1:非パーティション表のバックアップ

まず、次のコマンドを実行して表TABLEAの完全エクスポート・バックアップを作成します。

expdp \"/ as sysdba\" directory=EXPDP_DIR dumpfile=tableA_UNPAR.dmp logfile=tableA_UNPAR.log TABLES=TEST.TABLEA

expdp \"/ as sysdba\" directory=EXPDP_DIR dumpfile=tableA_metaunpar.dmp logfile=tableA_metaunpar.log TABLES=TEST.TABLEA content=metadata_only

ステップ2:データベースオブジェクトの確認

表を削除したときに連動して削除される依存オブジェクト(D)には、以下のようなものがあります。

CONSTRAINT(制約)
INDEX(索引)
MATERIALIZED_VIEW_LOG(マテリアライズド・ビュー・ログ)
OBJECT_GRANT(オブジェクト権限)
TRIGGER(トリガー)

次のSQLコマンドを実行し、出力をcons_trig_indx.txtなどのスプールファイルに保存しておきましょう。

set LINESIZE 500
set PAGESIZE 1000
SQL> spool cons_trig_indx.txt
SQL> select name, type, owner from all_dependencies where referenced_owner = 'TEST' and referenced_name = 'TABLEA';

NAME                TYPE              OWNER
--------------      --------------    -------
PROC_TABLEA         PROCEDURE         TEST
TABLEA_TRIGG        TRIGGER           TEST
PKG_TABLEA          PACKAGE BODY      TEST


SQL> select OWNER, INDEX_NAME, TABLE_OWNER, TABLE_NAME, STATUS, TABLESPACE_NAME
from dba_indexes where TABLE_OWNER='TEST' and TABLE_NAME='TABLEA';

OWNER   INDEX_NAME       TABLE_OWNER  TABLE_NAME   STATUS   TABLESPACE_NAME
---------------------------------------------------------------------------
TEST    TABLEA_IDX_ID01    TEST        TABLEA      VALID    TABLEA_TBL
TEST    TABLEA_IDX_ID04    TEST        TABLEA      VALID    TABLEA_TBL
TEST    TABLEA_IDX_PK      TEST        TABLEA      VALID    TABLEA_TBL


SQL> select STATUS, OBJECT_TYPE, OBJECT_NAME  from dba_objects
where OWNER='TEST' and OBJECT_TYPE = 'TRIGGER' and STATUS='INVALID';

no rows selected

SQL> select CONSTRAINT_NAME, CONSTRAINT_TYPE from dba_constraints
where TABLE_NAME='TABLEA' and owner='TEST';
SQL> spool off

CONSTRAINT_NAME     C
------------------  -----
SYS_C002004601      C
SYS_C002004602      C
SYS_C002004603      C
IDX_PK              P
FK01                R

ステップ3:TABLEAのDDLを取得する

パーティション表を作成する前に、次のコマンドを実行してTABLEAのDDL(データ定義言語)を取得し、スプールファイルDEF_TABLEA.sqlに保存します。

set echo off
set feedback off
set linesize 160
set long 2000000
set pagesize 0
set trims on
column txt format a150 word_wrapped
SQL> spool DEF_TABLEA.sql
SQL> select DBMS_METADATA.GET_DDL('TABLE','TABLEA','TEST') txt FROM dual;
SQL> spool off

ステップ4:DDLスクリプトをコピーする

ステップ3で作成したDDLスクリプトを、次のコマンドでコピーします。

cp DEF_TABLEA.sql DEF_TABLEA_PAR.sql

ステップ5:非パーティション表の日付データを確認する

TABLEAに格納されている日付を把握するため、次のコマンドを実行します。

SQL> select * from (select DT from TEST.TABLEA where rownum <15 order by DT DESC);

ステップ6:DEF_TABLEA_PAR.sqlファイルを編集する

DEF_TABLEA_PAR.sqlを編集し、以下の変更を加えます。

すべてのTABLEAをTABLEA_PARに置換する。
NOT NULLをはじめとするすべての制約定義を削除する。
新しい表領域に表を作成するため、次の句を挿入する。
```
  TABLESPACE \"TABLEA_TBL_PAR\" LOGGING
```

ステップ5で確認した日付に基づき、次のパーティション定義を追加する。

  PARTITION BY RANGE(DT)
  interval (numtoyminterval(1,'MONTH'))
  (partition TABLEA_2004  values less than  (to_date('01/01/2005','DD/MM/YYYY')),
   partition TABLEA_2005 values less than  (to_date('01/01/2006','DD/MM/YYYY')));

編集後のDEF_TABLEA_PAR.sqlは、次のようになります。

CREATE TABLE \"TEST\".\"TABLEA_PAR\"
(    \"ID\" NUMBER(6,0),
     \"CEID\" NUMBER(6,0),
     \"DT\" DATE,
     \"AMT\" NUMBER(14,4),
     \"RET\" NUMBER(14,4),
     \"CNT\" NUMBER(4,0),
     \"VCNT\" NUMBER(4,0),
     \"EXEDT\" DATE,
     \"LASTUPDBY\" VARCHAR2(15),
     \"VENUM\" NUMBER(6,0),
     \"LASTUPDDT\" TIMESTAMP (6))
TABLESPACE \"TABLEA_TBL_PAR\" LOGGING
PARTITION BY RANGE(DT)
interval (numtoyminterval(1,'MONTH'))
(partition TABLEA_2004  values less than  (to_date('01/01/2005','DD/MM/YYYY')),
 partition TABLEA_2005  values less than  (to_date('01/01/2006','DD/MM/YYYY')));

ステップ7:パーティション表を作成する

DEF_TABLEA_PAR.sqlスクリプトを実行して、パーティション表を作成します。

SQL> spool DEF_TABLEA_PAR.outp.txt
SQL> @DEF_TABLEA_PAR.sql

Table Created.

SQL> spool off

ステップ8:パーティション表を検証する

次のコマンドでパーティション表を検証し、定義されたパーティションを確認します。

SQL> spool verify_partition.txt
SQL> select partition_name from DBA_tab_partitions where table_name ='TABLEA_PAR' and table_owner = 'TEST';
SQL> spool off

PARTITION_NAME
-----------------
TABLEA_2004
TABLEA_2005

ステップ9:非パーティション表の統計情報を収集する

次のコマンドで非パーティション表の統計情報を収集し、スプールファイルに保存します。

SQL> SPOOL gather_stats.txt
SQL> exec dbms_stats.gather_table_stats ('TEST', 'TABLEA',cascade => TRUE);
SQL> spool off

ステップ10:再定義の可否をチェックする

ポイント: 再定義パッケージを使用する前に、ソース表(非パーティション表)に主キーは必要ありません。

次のコマンドで再定義が可能かどうかを確認し、結果をスプールファイルに保存します。

SQL> spool check_the_redefinition.txt
SQL> EXEC DBMS_Redefinition.can_redef_table ('TEST', 'TABLEA');
SQL> spool off

ステップ11:再定義を開始する

check_the_redefinition.txtにエラーが出力されていないことを確認したら、次の長時間実行コマンドで再定義を開始します。

SQL> spool start_redef_table.txt
SQL>begin
    dbms_redefinition.start_redef_table
    (
     uname => 'TEST',
     orig_table => 'TABLEA',
     int_table => 'TABLEA_PAR');
     end;
   /
SQL> spool off

ステップ12:再定義中の表領域エラーに注意する

ステップ11の再定義処理では、次のような表領域に関するアラートが発生する場合があります。

ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-01688: unable to extend table TEST.TABLEA_PAR
partition SYS_P42 by 1024 in tablespace TABLEA_TBL
ORA-06512: at \"SYS.DBMS_REDEFINITION\", line 52
ORA-06512: at \"SYS.DBMS_REDEFINITION\", line 1646
ORA-06512: at line 2

ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-14400: inserted partition key does not map to any partition
ORA-06512: at \"SYS.DBMS_REDEFINITION\", line 52
ORA-06512: at \"SYS.DBMS_REDEFINITION\", line 1646
ORA-06512: at line 2

上記のような表領域エラーが発生した場合は、以下の手順を実施してください。

次のコマンドで再定義プロセスを中断します。

 SQL> spool abort_redef_table.txt
 SQL> begin
      dbms_redefinition.abort_redef_table
      (
      uname => 'TEST',
      orig_table => 'TABLEA',
      int_table => 'TABLEA_PAR');
      end;
     /
 SQL> spool off

パーティション表とマテリアライズド・ビューを削除します。
表領域のサイズを拡張します。この例では、表領域TABLEA_TBLのサイズを増やします。
ステップ11を再実行します。

ステップ13:再定義エラーの有無を確認する

再定義プロセスが正常に完了した後、次のコマンドでエラーの有無をチェックします。

SQL> spool copy_table_dependents.txt
SQL> SET SERVEROUTPUT ON
     DECLARE
     l_num_errors PLS_INTEGER;
     BEGIN
       DBMS_REDEFINITION.copy_table_dependents(
           uname             => 'TEST',
           orig_table        => 'TABLEA',
           int_table         => 'TABLEA_PAR',
           copy_indexes      => DBMS_REDEFINITION.cons_orig_params, -- Non-Default
           num_errors        => l_num_errors);
           DBMS_OUTPUT.put_line('l_num_errors=' || l_num_errors);
     END;
/
SQL> spool off

再定義が成功していれば、copy_table_dependents.txtに次のような結果が出力されます。

l_num_errors=0
PL/SQL procedure successfully completed.

ステップ14:(任意)パーティション表を再同期する

必要に応じて、次のコマンドで中間表との再同期を実行できます。

SQL> spool sync_interim_table.txt
SQL>
     BEGIN
       DBMS_REDEFINITION.sync_interim_table
       (
           uname => 'TEST',
           orig_table => 'TABLEA',
           int_table => 'TABLEA_PAR');
      END;
/
SQL> spool off

ステップ15:パーティション表の統計情報を収集する

次のコマンドでパーティション表の統計情報を収集します。

SQL> spool gather_statistics_par.txt
SQL> exec dbms_stats.gather_table_stats ('TEST', 'TABLEA_PAR',cascade => TRUE);
SQL> spool off

ステップ16:制約有効化スクリプトを作成する

validate制約を有効化するためのスクリプトを、次のコマンドで準備します。

SQL> spool constraint_enable_validate.txt
SET LINESIZE 500
SET PAGESIZE 1000

SQL> select 'alter table' ||' '||OWNER||'.'||TABLE_NAME||' enable validate constraint'||' '||CONSTRAINT_NAME||';' from dba_constraints where TABLE_NAME = 'TABLEA_PAR' and OWNER='TEST';

'ALTERTABLE'||''||OWNER||'.'||TABLE_NAME||'ENABLEVALIDATECONSTRAINT'||''||CONSTR
--------------------------------------------------------------------------------
alter table TEST.TABLEA_PAR enable validate constraint TMP$$_SYS_C002004601;
alter table TEST.TABLEA_PAR enable validate constraint TMP$$_SYS_C002004602;
alter table TEST.TABLEA_PAR enable validate constraint TMP$$_SYS_C002004603;
alter table TEST.TABLEA_PAR enable validate constraint TMP$$_IDX_PK;
alter table TEST.TABLEA_PAR enable validate constraint TMP$$_FK01;

SQL> spool off

ステップ17:validate制約を有効化する

ステップ16で生成されたスクリプトとコマンドを、次のように実行します。

SQL> spool constraint_enable_execute.outp.txt
SQL>@constraint_enable.sql

alter table TEST.TABLEA_PAR enable validate constraint TMP$$_SYS_C002004601;
alter table TEST.TABLEA_PAR enable validate constraint TMP$$_SYS_C002004602;
alter table TEST.TABLEA_PAR enable validate constraint TMP$$_SYS_C002004603;
alter table TEST.TABLEA_PAR enable validate constraint TMP$$_IDX_PK;
alter table TEST.TABLEA_PAR enable validate constraint TMP$$_FK01;

SQL> spool off

ステップ18:非パーティション表とパーティション表を比較する

元の非パーティション表と新しいパーティション表を比較し、すべての属性が同一であることを確認します。

ステップ19:表名を切り替える

次のコマンドで中間表を正式な表として設定し、表名を切り替えます。

SQL> spool finish_redef_table.txt
     BEGIN
       DBMS_REDEFINITION.finish_redef_table
      (
        uname => 'TEST',
        orig_table => 'TABLEA',
        int_table => 'TABLEA_PAR');
     END;
/

--------------------------------------------
@?/rdbms/admin/utlrp.sql
--------------------------------------------

SQL>spool off

ステップ20:両表のレコード数を比較する

次のコマンドで両方の表のレコード数を比較し、件数が一致していることを確認します。

SQL> spool table_count.outp.txt
SQL> select count(*) from TEST.TABLEA;

 COUNT(*)
----------
  890540

SQL> select count (*) from TEST.TABLEA_PAR;

 COUNT(*)
----------
  890540

SQL> spool off

ステップ21:パーティション化の成功を検証する

次のコマンドで、パーティション化が正しく完了したことを確認します。

SQL> spool check_partition.txt
SQL> select partitioned from dba_tables where table_name = 'TABLEA' and owner='TEST';

PAR
------
YES

SQL> select partition_name , SUBPARTITION_COUNT, TABLESPACE_NAME from dba_tab_partitions where table_name='TABLEA' and table_owner='TEST';
SQL> select table_name, partition_name, high_value, partition_position from DBA_tab_partitions where table_name='TABLEA' and table_owner='TEST';
SQL> spool off

ステップ22:データベースオブジェクトを再確認する

次のコマンドでデータベースオブジェクトを調査し、その結果をステップ2の出力と比較します。

SET LINESIZE 500
SET PAGESIZE 1000
SQL> spool cons_indx_trigg.txt
SQL> select name, type, owner from all_dependencies where referenced_owner = 'TEST' and referenced_name = 'TABLEA';

NAME                TYPE              OWNER
----------------    ---------------   ------------
PROC_TABLEA         PROCEDURE         TEST
TABLEA_TRIGG        TRIGGER           TEST
PKG_TABLEA          PACKAGE BODY      TEST

SQL> select OWNER, INDEX_NAME, TABLE_OWNER, TABLE_NAME, STATUS, TABLESPACE_NAME from dba_indexes where TABLE_OWNER='TEST' and TABLE_NAME='TABLEA';

OWNER  INDEX_NAME       TABLE_OWNER TABLE_NAME  STATUS   TABLESPACE_NAME
------------------------------------------------------------------------
TEST   TABLEA_IDX_ID01  TEST        TABLEA      VALID    TABLEA_TBL
TEST   TABLEA_IDX_ID04  TEST        TABLEA      VALID    TABLEA_TBL
TEST   TABLEA_IDX_PK    TEST        TABLEA      VALID    TABLEA_TBL

SQL> select STATUS, OBJECT_TYPE, OBJECT_NAME  from dba_objects where OWNER='TEST' and OBJECT_TYPE = 'TRIGGER' and STATUS='INVALID';

no rows selected

SQL> select CONSTRAINT_NAME, CONSTRAINT_TYPE from dba_constraints where TABLE_NAME='TABLEA' and owner='TEST';

CONSTRAINT_NAME        C
-------------------		----------
SYS_C002004601         C
SYS_C002004602         C
SYS_C002004603         C
IDX_PK                 P
FK01                   R

12 rows selected.

SQL> spool off

ステップ23:索引を再構築する

次のコマンドで、新しい表領域上に索引を再構築します。

SQL> spool rebuild_indx.txt
SQL>@rebuild_index.sql

ALTER INDEX TEST.TABLEA_IDX_ID01 REBUILD TABLESPACE TABLEA_TBL_PAR ONLINE;
ALTER INDEX TEST.ITABLEA_IDX_ID04 REBUILD TABLESPACE TABLEA_TBL_PAR ONLINE;
ALTER INDEX TEST.TABLEA_IDX_PK REBUILD TABLESPACE TABLEA_TBL_PAR ONLINE;

SQL> spool off

ステップ24:索引を検証する

次のコマンドですべての索引のステータスがVALIDであり、表領域がTABLEA_TBL_PARになっていることを確認します。

SQL> spool verify_indx.outp.txt
SQL> select OWNER, INDEX_NAME, TABLE_OWNER, TABLE_NAME, STATUS, TABLESPACE_NAME from dba_indexes where TABLE_OWNER='TEST' and TABLE_NAME='TABLEA';

OWNER  INDEX_NAME       TABLE_OWNER  TABLE_NAME   STATUS   TABLESPACE_NAME
---------------------------------------------------------------------------
TEST   TABLEA_IDX_ID01  TEST         TABLEA       VALID   	 TABLEA_TBL_PAR
TEST   TABLEA_IDX_ID04  TEST         TABLEA       VALID   	 TABLEA_TBL_PAR
TEST   TABLEA_IDX_PK    TEST         TABLEA       VALID     TABLEA_TBL_PAR

SQL>spool off

ステップ25:元の非パーティション表を削除する

DBAがすべての項目に問題ないことを確認した後、中間表の名前となった元の表(TEST.TABLEA_PAR)を次のコマンドで削除します。

SQL> DROP table TEST.TABLEA_PAR cascade constraints;

まとめ

以上の手順では、中間表TEST.TABLEA_PARを活用することで、アプリケーションのダウンタイムを発生させることなく、表TEST.TABLEAをレンジ・インターバル・パーティション表へと移行できました。

ご意見やご質問がある場合は、フィードバックタブからお気軽にお寄せください。

Oracle E-Business SuiteをOATM（Oracle Applications Tablespace Model）へ移行する方法：移行ユーティリティ完全ガイド

SQL Server 2016で非推奨・廃止された機能一覧｜データベース移行前に確認すべきポイント

Oracle 19cのDBCAコマンドでデータベースをクローンする方法【サイレントモード完全ガイド】
本記事では、Oracle Database 19cの新機能であるDatabase Configuration Assistant（DBCA）を使用して、ソースデータベースのバックアップを作成することなく、リモートのプラガブル・データベース（PDB）をコンテナ・データベース（CDB）へクローンする手順をご紹介します。 DBCAによるクローンの最大の特長は、ソースからターゲットへの複製にかかる時間が最小限に抑えられる点です。ソースDBの構成 CDB：LCONCDB PDB：LCON 以下は、ソース側の各コンテナ（CDBおよびPDB）に存在するDBFファイルの総数です。クローン作成後は、ター
OracleのSQLプロファイルとSQLプラン・ベースラインの違いと活用方法
本記事では、Oracle®におけるSQLプロファイルとSQLプラン・ベースラインの違いを解説し、クエリチューニング時にそれぞれがどのように機能するのかを詳しく説明します。オプティマイザ、プロファイル、ベースラインの関係これら3つの要素は、大まかに以下のように連携して動作します。クエリオプティマイザは、システム統計、バインド変数、コンパイル情報などを基に、クエリ実行に最適なプランを導き出します。しかし、入力情報に不備があると、必ずしも最適とは言えないプランを選択してしまうことがあります。SQLプロファイルには、こうした問題を緩和するための補助情報が含まれています。これによりオプティマイザの判断ミ