スキーマのオブジェクト定義比較
スキーマ間の diff の必要性
同じアプリケーションを複数1つのOracleデータベース上で動作させる場合、『スキーマ(Oracleの場合ユーザと同じ)』を複数用意すればよい。ところが、『それら複数のスキーマの持っているテーブルやその他のオブジェクトは全て同一なのか？同期がとれているのか？』という問題に見舞われることが多々ある。特に次の様なタイミングで見舞われることが多い。
- 初期のセットアップ時
- 移行時
- 試験中・運用中にオブジェクト定義が変更された時
武器
オブジェクトの定義を比較する上では、オブジェクトを定義するためのSQL(DDL)を比較する方法が一番安心感を与えてくれる。そのDDLを用いた比較を実現するためのPL/SQLパッケージは次の2つ。
DBMS_METADATA はDDLを取得するためのインタフェースであり、DDLを生成する仕組みの大半はこれをバックエンドで動かしている(はず)。
DBMS_METADATA_DIFF の微妙さ
DBMS_METADATA_DIFF は名前からして明らかにニーズにマッチしそうなので動作を確認してみた。
使用する状況を考えれば、大半のオブジェクトは流石に一致するので、『同じか、違うか』をまず判断し、『違う』ものにフォーカスして調査をすれば実用に耐える。なので『同じか、違うか』を判断したいのだが、DBMS_METADATA_DIFF に同じかどうかを判定するプロシージャやファンクションは直接存在しない。
DBMS_METADATA_DIFF.COMPARE_ALTER は『最初のオブジェクトを2番目のオブジェクトのようにするための一連のALTER文を戻します』とのことなので、『何も返って来なければ等しい』と期待したのだが、実際そうならない。TABLE についてはある程度その期待に応えてくれるのだが、全く違う列をもつ VIEW 同士を比較してもこのプロシージャは何も ALTER 文を返して来ない。ALTER 文で VIEW 定義を直すことはできない (作り直せ) から何も返さない(返せない)ということなのだろう。
そんなわけで、DBMS_METADATA_DIFF は使えるようでありながら今回は使えない。
DBMS_METADATA で DDL を比較する実装
基本方針
- DBA_OBJECTS から比較する両スキーマのオブジェクトを取り出す。
- 比較・判断方法
- DDL上の次の項目は違いを無視する (状況により変わることがあるかも)
- 各オブジェクトのタイプごとに件数レポートを出力する。
- 比較に使用したDDLを(望めば)表示させられるようにする。
比較上重要ではない差分について反応することを避けるために、DBMS_METADATA は都合の良い仕組みを持っている。表領域をDDLに含めないとか、スキーマ名を変更するとか、そういったことを ADD_TRANSFORM, SET_TRANSFORM_PARAM, SET_REMAP_PARAM の 3プロシージャを使用することで容易に行える。ただし、元々想定されている以上の変換(表示させないなど)をしようとすると ADD_TRANSFORM に独自定義の XSLT を与えることになる。
XSLT定義 ( /home/oracle/COMPARE_SCHEMA.xslt )
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:template match="/"> <xsl:apply-templates /> </xsl:template> <xsl:template match="@*|node()"> <xsl:copy> <xsl:apply-templates select="@*|node()" /> </xsl:copy> </xsl:template> <!-- TABLE --> <!-- ignore STORAGE(INITIAL, NEXT, MINEXTENT, PCTINCREASE) --> <xsl:template match="//TABLE_T/DEFERRED_STG/INITIAL_STG"/> <xsl:template match="//TABLE_T/DEFERRED_STG/NEXT_STG"/> <xsl:template match="//TABLE_T/DEFERRED_STG/MINEXT_STG"/> <xsl:template match="//TABLE_T/DEFERRED_STG/PCTINC_STG"/> <!-- ignore PCTFREE, PCTUSED --> <xsl:template match="//TABLE_T/PCT_USED"><xsl:copy>IGNORED</xsl:copy></xsl:template> <xsl:template match="//TABLE_T/PCT_FREE"><xsl:copy>IGNORED</xsl:copy></xsl:template> <!-- SEQUENCE --> <!-- ignore START WITH --> <xsl:template match="//SEQUENCE_T/HIGHWATER"><xsl:copy>IGNORED</xsl:copy></xsl:template> </xsl:stylesheet>
XSLT に変換を追加する場合は、次の SQL で変換対象の XML ドキュメントを確認できる。
SELECT DBMS_METADATA.GET_XML('オブジェクトタイプ','オブジェクト名','スキーマ名') FROM DUAL;
COMPARE_SCHEMA プロシージャ定義
2行目で前述の XSLT のパスを定義しているので保存場所を指定すること。
(特に DIRECTORY オブジェクトの下じゃなくても読めてしまう)
CREATE OR REPLACE PROCEDURE COMPARE_SCHEMA( user1 VARCHAR2, user2 VARCHAR2, printDDL NUMBER := 0 ) AS xslt VARCHAR2(256) := '/home/oracle/COMPARE_SCHEMA.xslt'; ddl1 CLOB; ddl2 CLOB; TYPE OBJTYPECOUNT IS VARRAY(5) OF NUMBER; L CONSTANT NUMBER := 1; R CONSTANT NUMBER := 2; S CONSTANT NUMBER := 3; D CONSTANT NUMBER := 4; N CONSTANT NUMBER := 5; TYPE OBJCOUNT IS TABLE OF OBJTYPECOUNT INDEX BY VARCHAR2(30); counter OBJCOUNT; CURSOR objects IS SELECT NVL(OBJ1.OBJECT_TYPE, OBJ2.OBJECT_TYPE) OBJECT_TYPE, NVL(OBJ1.OBJECT_NAME, OBJ2.OBJECT_NAME) OBJECT_NAME, NVL2(OBJ1.OBJECT_NAME, 'Y', 'N') DEFINED1, NVL2(OBJ2.OBJECT_NAME, 'Y', 'N') DEFINED2 FROM (SELECT * FROM DBA_OBJECTS WHERE OWNER = user1) OBJ1 FULL OUTER JOIN (SELECT * FROM DBA_OBJECTS WHERE OWNER = user2) OBJ2 ON OBJ1.OBJECT_TYPE = OBJ2.OBJECT_TYPE AND OBJ1.OBJECT_NAME = OBJ2.OBJECT_NAME ORDER BY OBJECT_TYPE, OBJECT_NAME ; CURSOR objectTypes IS SELECT DISTINCT OBJECT_TYPE FROM DBA_OBJECTS WHERE OWNER IN (user1, user2) ORDER BY OBJECT_TYPE ; CANNOT_GET_DDL CONSTANT VARCHAR2(32) := 'CANNOT_GET_DDL'; PROCEDURE GET_DDL( objectType VARCHAR2, owner VARCHAR2, objectName VARCHAR2, ddl IN OUT NOCOPY CLOB ) AS openHandle NUMBER; transHandle NUMBER; invalid_input_value EXCEPTION; PRAGMA EXCEPTION_INIT(invalid_input_value,-31600); BEGIN BEGIN openHandle := DBMS_METADATA.OPEN(objectType); EXCEPTION WHEN invalid_input_value THEN ddl := CANNOT_GET_DDL; RETURN; END; DBMS_METADATA.SET_PARSE_ITEM(openHandle, 'OBJECT_TYPE'); DBMS_METADATA.SET_PARSE_ITEM(openHandle, 'NAME'); DBMS_METADATA.SET_FILTER(openHandle, 'NAME', objectName); DBMS_METADATA.SET_FILTER(openHandle, 'SCHEMA', owner); --付け替え(リマップ)用の変換を追加 transHandle := DBMS_METADATA.ADD_TRANSFORM(openHandle, 'MODIFY'); DBMS_METADATA.SET_REMAP_PARAM(transHandle, 'REMAP_SCHEMA', owner, 'USER'); --XMLに対して独自のXSLTを適用(比較上余計な差分を消すなど) transHandle := DBMS_METADATA.ADD_TRANSFORM(openHandle, xslt); --DDLの出力を得るための変換を追加(FETCH_DDL呼び出しが可能になる) transHandle := DBMS_METADATA.ADD_TRANSFORM(openHandle, 'DDL'); DBMS_METADATA.SET_TRANSFORM_PARAM(transHandle, 'PRETTY', FALSE); DBMS_METADATA.SET_TRANSFORM_PARAM(transHandle, 'SQLTERMINATOR', FALSE); BEGIN DBMS_METADATA.SET_TRANSFORM_PARAM(transHandle, 'TABLESPACE', FALSE); EXCEPTION WHEN invalid_input_value THEN NULL; END; ddl := DBMS_METADATA.FETCH_CLOB(openHandle); DBMS_METADATA.CLOSE(openHandle); --前後に余計な改行や空白が残るため除去 ddl := TRIM(REPLACE(REPLACE(ddl,CHR(10),' '),CHR(13),' ')); END; PROCEDURE PRINT_RESULT( objectType VARCHAR2, objectName VARCHAR2, result NUMBER ) AS prefix VARCHAR2(16); BEGIN CASE result WHEN S THEN prefix := ' '; WHEN D THEN prefix := 'D '; WHEN L THEN prefix := 'L '; WHEN R THEN prefix := 'R '; WHEN N THEN prefix := 'N '; END CASE; DBMS_OUTPUT.PUT_LINE(prefix||RPAD(objectType,20,' ')||' '||RPAD(objectName,30,' ')); END; PROCEDURE INCREMENT_COUNTER( objectType VARCHAR2, result NUMBER ) AS BEGIN counter(objectType)(result) := counter(objectType)(result) + 1; counter('TOTAL')(result) := counter('TOTAL')(result) + 1; END; PROCEDURE PRINT_HEADER AS BEGIN DBMS_OUTPUT.PUT_LINE('LEFT - '||user1); DBMS_OUTPUT.PUT_LINE('RIGHT - '||user2); DBMS_OUTPUT.PUT_LINE(''); DBMS_OUTPUT.PUT_LINE( RPAD('OBJECT TYPE',20,' ') || ' '||LPAD('ALL',6,' ') || ' '||LPAD('SAME',6,' ') || ' '||LPAD('DIFF',6,' ') || ' '||LPAD('LEFT',6,' ') || ' '||LPAD('RIGHT',6,' ') || ' '||LPAD('NOTICE',6,' ')); END; PROCEDURE PRINT_COUNTER( k VARCHAR2 ) AS subtotal NUMBER; BEGIN subtotal := COUNTER(k)(S)+COUNTER(k)(D)+COUNTER(k)(L)+COUNTER(k)(R)+COUNTER(k)(N); DBMS_OUTPUT.PUT_LINE( RPAD(k,20,' ') || TO_CHAR(subtotal,'999999') || TO_CHAR(COUNTER(k)(S),'999999') || TO_CHAR(COUNTER(k)(D),'999999') || TO_CHAR(COUNTER(k)(L),'999999') || TO_CHAR(COUNTER(k)(R),'999999') || TO_CHAR(COUNTER(k)(N),'999999')); END; BEGIN -- カウンター初期化 FOR t IN objectTypes LOOP counter(t.OBJECT_TYPE) := OBJTYPECOUNT(0,0,0,0,0); END LOOP; counter('TOTAL') := OBJTYPECOUNT(0,0,0,0,0); -- オブジェクト比較 FOR o IN objects LOOP IF o.DEFINED2 = 'N' THEN -- スキーマ1でのみ定義されている PRINT_RESULT(o.OBJECT_TYPE, o.OBJECT_NAME, L); INCREMENT_COUNTER(o.OBJECT_TYPE, L); CONTINUE; ELSIF o.DEFINED1 = 'N' THEN -- スキーマ2のみで定義されている PRINT_RESULT(o.OBJECT_TYPE, o.OBJECT_NAME, R); INCREMENT_COUNTER(o.OBJECT_TYPE, R); CONTINUE; END IF; --両スキーマで定義されているのでDDL比較で完全一致を確認する GET_DDL(o.OBJECT_TYPE, user1, o.OBJECT_NAME, ddl1); GET_DDL(o.OBJECT_TYPE, user2, o.OBJECT_NAME, ddl2); IF ddl1 = CANNOT_GET_DDL OR ddl2 = CANNOT_GET_DDL THEN --DDL取得ができないオブジェクト PRINT_RESULT(o.OBJECT_TYPE, o.OBJECT_NAME, N); INCREMENT_COUNTER(o.OBJECT_TYPE, N); CONTINUE; END IF; IF ddl1 = ddl2 THEN --DDL一致 PRINT_RESULT(o.OBJECT_TYPE, o.OBJECT_NAME, S); INCREMENT_COUNTER(o.OBJECT_TYPE, S); IF printDDL >= 2 THEN DBMS_OUTPUT.PUT_LINE(' LR: '||ddl1); END IF; CONTINUE; END IF; --DDL不一致 PRINT_RESULT(o.OBJECT_TYPE, o.OBJECT_NAME, D); INCREMENT_COUNTER(o.OBJECT_TYPE, D); IF printDDL >= 1 THEN DBMS_OUTPUT.PUT_LINE(' *L: '||ddl1); DBMS_OUTPUT.PUT_LINE(' *R: '||ddl2); END IF; END LOOP; -- レポート出力 DBMS_OUTPUT.PUT_LINE(''); DBMS_OUTPUT.PUT_LINE(''); DBMS_OUTPUT.PUT_LINE(RPAD('*',80,'*')); PRINT_HEADER(); DBMS_OUTPUT.PUT_LINE(RPAD('-',80,'-')); FOR t IN objectTypes LOOP PRINT_COUNTER(t.OBJECT_TYPE); END LOOP; DBMS_OUTPUT.PUT_LINE(RPAD('-',80,'-')); PRINT_COUNTER('TOTAL'); END; /
使用例
比較に使用したDDLを表示しない場合
EXECUTE COMPARE_SCHEMA('SCHEMA1', 'SCHEMA2', 0);
INDEX SAME_INDEX SEQUENCE SAME_SEQUENCE SYNONYM SAME_SYNONYM D TABLE DIFF_TABLE D TABLE DIFF_TABLE2 TABLE SAME_TABLE L TABLE SAMPLE L TRIGGER SAMPLE_TG1 D VIEW DIFF_VIEW VIEW SAME_VIEW ******************************************************************************** LEFT - SCHEMA1 RIGHT - SCHEMA2 OBJECT TYPE ALL SAME DIFF LEFT RIGHT NOTICE -------------------------------------------------------------------------------- INDEX 1 1 0 0 0 0 SEQUENCE 1 1 0 0 0 0 SYNONYM 1 1 0 0 0 0 TABLE 4 1 2 1 0 0 TRIGGER 1 0 0 1 0 0 VIEW 2 1 1 0 0 0 -------------------------------------------------------------------------------- TOTAL 10 5 3 2 0 0
比較に使用したDDLを表示する場合(差分があるものだけ)
EXECUTE COMPARE_SCHEMA('SCHEMA1', 'SCHEMA2', 1);
INDEX SAME_INDEX
SEQUENCE SAME_SEQUENCE
SYNONYM SAME_SYNONYM
D TABLE DIFF_TABLE
*L: CREATE TABLE "USER"."DIFF_TABLE" ("ID" NUMBER(10,0), "COL1" NUMBER(20,0) DEFAULT 0, "COL2" NUMBER NOT NULL ENABLE) SEGMENT CREATION DEFERRED PCTFREE IGNORED PCTUSED IGNORED INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
*R: CREATE TABLE "USER"."DIFF_TABLE" ("ID" NUMBER(5,0), "COL1" NUMBER(20,0) DEFAULT 5, "COL2" NUMBER) SEGMENT CREATION DEFERRED PCTFREE IGNORED PCTUSED IGNORED INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
D TABLE DIFF_TABLE2
*L: CREATE TABLE "USER"."DIFF_TABLE2" ("ID" NUMBER(10,0)) SEGMENT CREATION DEFERRED PCTFREE IGNORED PCTUSED IGNORED INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
*R: CREATE TABLE "USER"."DIFF_TABLE2" ("ID" NUMBER(10,0)) SEGMENT CREATION DEFERRED PCTFREE IGNORED PCTUSED IGNORED INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE( BUFFER_POOL KEEP FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLE SAME_TABLE
L TABLE SAMPLE
L TRIGGER SAMPLE_TG1
D VIEW DIFF_VIEW
*L: CREATE OR REPLACE FORCE VIEW "USER"."DIFF_VIEW" ("ID", "COL1") AS SELECT id, col1 FROM DIFF_TABLE
*R: CREATE OR REPLACE FORCE VIEW "USER"."DIFF_VIEW" ("ID", "COL2") AS SELECT id, col2 FROM DIFF_TABLE
VIEW SAME_VIEW
********************************************************************************
LEFT - SCHEMA1
RIGHT - SCHEMA2
OBJECT TYPE ALL SAME DIFF LEFT RIGHT NOTICE
--------------------------------------------------------------------------------
INDEX 1 1 0 0 0 0
SEQUENCE 1 1 0 0 0 0
SYNONYM 1 1 0 0 0 0
TABLE 4 1 2 1 0 0
TRIGGER 1 0 0 1 0 0
VIEW 2 1 1 0 0 0
--------------------------------------------------------------------------------
TOTAL 10 5 3 2 0 0
比較に使用したDDLを表示する場合(差分の有無にかかわらず)
EXECUTE COMPARE_SCHEMA('SCHEMA1', 'SCHEMA2', 2);
INDEX SAME_INDEX
LR: CREATE INDEX "USER"."SAME_INDEX" ON "USER"."SAME_TABLE" ("ID") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOCOMPRESS LOGGING
SEQUENCE SAME_SEQUENCE
LR: CREATE SEQUENCE "USER"."SAME_SEQUENCE" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH IGNORED CACHE 20 NOORDER NOCYCLE
SYNONYM SAME_SYNONYM
LR: CREATE OR REPLACE SYNONYM "USER"."SAME_SYNONYM" FOR "USER"."SAME_TABLE"
D TABLE DIFF_TABLE
*L: CREATE TABLE "USER"."DIFF_TABLE" ("ID" NUMBER(10,0), "COL1" NUMBER(20,0) DEFAULT 0, "COL2" NUMBER NOT NULL ENABLE) SEGMENT CREATION DEFERRED PCTFREE IGNORED PCTUSED IGNORED INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
*R: CREATE TABLE "USER"."DIFF_TABLE" ("ID" NUMBER(5,0), "COL1" NUMBER(20,0) DEFAULT 5, "COL2" NUMBER) SEGMENT CREATION DEFERRED PCTFREE IGNORED PCTUSED IGNORED INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
D TABLE DIFF_TABLE2
*L: CREATE TABLE "USER"."DIFF_TABLE2" ("ID" NUMBER(10,0)) SEGMENT CREATION DEFERRED PCTFREE IGNORED PCTUSED IGNORED INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
*R: CREATE TABLE "USER"."DIFF_TABLE2" ("ID" NUMBER(10,0)) SEGMENT CREATION DEFERRED PCTFREE IGNORED PCTUSED IGNORED INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE( BUFFER_POOL KEEP FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLE SAME_TABLE
LR: CREATE TABLE "USER"."SAME_TABLE" ("ID" NUMBER(20,0)) SEGMENT CREATION DEFERRED PCTFREE IGNORED PCTUSED IGNORED INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
L TABLE SAMPLE
L TRIGGER SAMPLE_TG1
D VIEW DIFF_VIEW
*L: CREATE OR REPLACE FORCE VIEW "USER"."DIFF_VIEW" ("ID", "COL1") AS SELECT id, col1 FROM DIFF_TABLE
*R: CREATE OR REPLACE FORCE VIEW "USER"."DIFF_VIEW" ("ID", "COL2") AS SELECT id, col2 FROM DIFF_TABLE
VIEW SAME_VIEW
LR: CREATE OR REPLACE FORCE VIEW "USER"."SAME_VIEW" ("ID") AS SELECT "ID" FROM SAME_TABLE
********************************************************************************
LEFT - SCHEMA1
RIGHT - SCHEMA2
OBJECT TYPE ALL SAME DIFF LEFT RIGHT NOTICE
--------------------------------------------------------------------------------
INDEX 1 1 0 0 0 0
SEQUENCE 1 1 0 0 0 0
SYNONYM 1 1 0 0 0 0
TABLE 4 1 2 1 0 0
TRIGGER 1 0 0 1 0 0
VIEW 2 1 1 0 0 0
--------------------------------------------------------------------------------
TOTAL 10 5 3 2 0 0
使用上の注意
- DBMS_OUTPUT を使う+空白を出力するので SET SERVEROUTPUT ON FORMAT WRAPPED を忘れずに(SQL*Plusの場合)。
- DBMS_OUTPUT したときにバッファ・オーバーフローになる場合にはバッファサイズを無制限などに変更する。
- NOTICE (DDLが生成できない) になるのはパーティションやLOBなど親となるオブジェクトから内部的に生成されるオブジェクトが多く、生成できなくて妥当な場合が多い。
- 動作確認は SYS ユーザで実施した(DBMS_METADATAを使えるユーザでの実行が必要)。
- 1オブジェクトごとに DBMS_METADATA のセットアップ・フェッチをする非効率仕様なのでパフォーマンスはよくない。
- XSLTの読み込みがうまくいかない場合は定義後に一度セッションの再接続をするとうまくいったりする。
