DBMS_COMPARISONパッケージ で表データの比較 #1 Tweet

Oracle Database 11gで新たに追加されたDBMS_COMPARISONパッケージ。
このパッケージは２つのTable/Single-table view/Materialized view（シノニムも可）を比較したり、データを一致させたりするための機能を提供するパッケージだ。
レプリケーション関連のマニュアルへのリンクもあるのでレプリケーション環境を意識した機能ではあるようだが、単に２つの表の内容を比較したり一致させることにも利用できるのでレプリケーション環境以外でも利用できる。

ただし、マニュアルに記載されているように利用できるデータ型や索引などの制限があるので、利用に際して事前調査は必要でしょうね。

参考：
Oracle Database PL/SQL Packages and Types Reference 11g Release 1 (11.1.) 31 DBMS_COMPARISON

ということで、早速試してみる。

環境は、先日作成したIBM Thinkpad R52 のCentOS 5上に構築したOracle Database 11g R1 11.1.0.6.0 for Linux (x86)を利用した。）

例によって、MacOSXのTerminalからssh経由で利用した。
（CentOS5/Oracle11gともエンコーディングにUTF-8を利用しているため、TerminalのエンコーティングもUTF-8に設定した。

まずは、Oracle Database 11gから新規追加されたDBMS_COMPARISONパッケージ関連のディクショナリービューの確認。（尚、DBA_＊ビューも追加されている。）

[oracle@glasscatfish ˜]$ sqlplus /nolog

SQL*Plus: Release 11.1.0.6.0 - Production on 月 10月 8 08:50:38 2007

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

> conn scott/tiger 
接続されました。
SCOTT> 
SCOTT> 
SCOTT> desc user_comparison_scan
 名前                                    NULL?    型
 ----------------------------------------- -------- ----------------------------
 COMPARISON_NAME                           NOT NULL VARCHAR2(30)
 SCAN_ID                                   NOT NULL NUMBER
 PARENT_SCAN_ID                                     NUMBER
 STATUS                                             VARCHAR2(16)
 COUNT_ROWS                                         NUMBER
 SCAN_NULLS                                         VARCHAR2(1)
 LAST_UPDATE_TIME                                   TIMESTAMP(6)


SCOTT> desc user_comparison_scan_summary
 名前                                    NULL?    型
 ----------------------------------------- -------- ----------------------------
 COMPARISON_NAME                           NOT NULL VARCHAR2(30)
 SCAN_ID                                   NOT NULL NUMBER
 PARENT_SCAN_ID                                     NUMBER
 ROOT_SCAN_ID                                       NUMBER
 STATUS                                             VARCHAR2(16)
 CURRENT_DIF_COUNT                                  NUMBER
 INITIAL_DIF_COUNT                                  NUMBER
 COUNT_ROWS                                         NUMBER
 SCAN_NULLS                                         VARCHAR2(1)
 LAST_UPDATE_TIME                                   TIMESTAMP(6)

SCOTT> desc user_comparison_scan_values
 名前                                    NULL?    型
 ----------------------------------------- -------- ----------------------------
 COMPARISON_NAME                           NOT NULL VARCHAR2(30)
 SCAN_ID                                   NOT NULL NUMBER
 COLUMN_POSITION                           NOT NULL NUMBER
 MIN_VALUE                                          VARCHAR2(4000)
 MAX_VALUE                                          VARCHAR2(4000)
 LAST_UPDATE_TIME                                   TIMESTAMP(6)

SCOTT> desc user_comparison_row_dif
 名前                                    NULL?    型
 ----------------------------------------- -------- ----------------------------
 COMPARISON_NAME                           NOT NULL VARCHAR2(30)
 SCAN_ID                                   NOT NULL NUMBER
 LOCAL_ROWID                                        ROWID
 REMOTE_ROWID                                       ROWID
 INDEX_VALUE                                        VARCHAR2(4000)
 STATUS                                             VARCHAR2(3)
 LAST_UPDATE_TIME                                   TIMESTAMP(6)

SCOTT> 
SCOTT> desc user_comparison
 名前                                    NULL?    型
 ----------------------------------------- -------- ----------------------------
 COMPARISON_NAME                           NOT NULL VARCHAR2(30)
 COMPARISON_MODE                                    VARCHAR2(5)
 SCHEMA_NAME                                        VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(30)
 OBJECT_TYPE                                        VARCHAR2(17)
 REMOTE_SCHEMA_NAME                                 VARCHAR2(30)
 REMOTE_OBJECT_NAME                                 VARCHAR2(30)
 REMOTE_OBJECT_TYPE                                 VARCHAR2(17)
 DBLINK_NAME                                        VARCHAR2(128)
 SCAN_MODE                                          VARCHAR2(9)
 SCAN_PERCENT                                       NUMBER
 CYCLIC_INDEX_VALUE                                 VARCHAR2(4000)
 NULL_VALUE                                         VARCHAR2(4000)
 LOCAL_CONVERGE_TAG                                 RAW(2000)
 REMOTE_CONVERGE_TAG                                RAW(2000)
 MAX_NUM_BUCKETS                                    NUMBER
 MIN_ROWS_IN_BUCKET                                 NUMBER
 LAST_UPDATE_TIME                                   TIMESTAMP(6)

SCOTT> 
SCOTT> desc user_comparison_columns
 名前                                    NULL?    型
 ----------------------------------------- -------- ----------------------------
 COMPARISON_NAME                           NOT NULL VARCHAR2(30)
 COLUMN_POSITION                           NOT NULL NUMBER
 COLUMN_NAME                               NOT NULL VARCHAR2(30)
 INDEX_COLUMN                                       VARCHAR2(1)

DBMS_COMPARISIONパッケージを利用するには、EXECUTEオブジェクト権限、及び、EXECUTE_CATALOG_ROLEロール（データディクショナリ内のオブジェクトに対するEXECUTE/SELECT権限）を実行するユーザへ付与する必要がある。

SCOTT> conn / as sysdba
接続されました。
SYS> grant execute on dbms_comparison to scott;

権限付与が成功しました。

SYS> grant execute_catalog_role to scott;

権限付与が成功しました。

SYS> conn scott/tiger
接続されました。
SCOTT> desc dbms_comparison
FUNCTION COMPARE RETURNS BOOLEAN
 引数名                         タイプ                  In/Out Default?
 ------------------------------ ----------------------- ------ --------
 COMPARISON_NAME                VARCHAR2                IN
 SCAN_INFO                      RECORD                  OUT
   SCAN_ID                      NUMBER                  OUT
   LOC_ROWS_MERGED              NUMBER                  OUT
   RMT_ROWS_MERGED              NUMBER                  OUT
   LOC_ROWS_DELETED             NUMBER                  OUT
   RMT_ROWS_DELETED             NUMBER                  OUT
 MIN_VALUE                      VARCHAR2                IN     DEFAULT
 MAX_VALUE                      VARCHAR2                IN     DEFAULT
 PERFORM_ROW_DIF                BOOLEAN                 IN     DEFAULT

・・・・・・・中略・・・・・・・

FUNCTION RECHECK RETURNS BOOLEAN
 引数名                         タイプ                  In/Out Default?
 ------------------------------ ----------------------- ------ --------
 COMPARISON_NAME                VARCHAR2                IN
 SCAN_ID                        NUMBER                  IN
 PERFORM_ROW_DIF                BOOLEAN                 IN     DEFAULT

SCOTT> 

これでDBMS_COMPARISONパッケージを利用する準備は整った。というところで本日は終わり。

次回へ続く。