DBLINKを使ったときのLOBの制約について

　この記事は JPOUG Advent Calendar 2021 - Adventar 22日目の記事です。21日目は羽田 久美子さんの記事「Oracle Data Safeでの監査データの取得」でした。羽田さんといえば、先日db tech showcaseでご講演もされており、益々ご活躍の様子、嬉しく思いました。

　今年を振り返ると、春に４年弱担当していたシステムの移行が終わったこと、新しい案件で本格的にOCIを触り始めることになったことなど、いくつか環境の変化がありました。また、先月、Oracle Groudbrakers APACで19c移行の性能についてオンライン講演できる機会を頂きました。CFPへの投稿にあたりHiroshi Sekiguchiさんにご協力頂き感謝しております。この場を借りてお礼申し上げます。講演のリプレイはしばらくの間こちら（Oracle 19c移行の性能検討ポイント）に公開されていますので、ご興味のある方は御覧ください。

１．はじめに

　DBLINKとは、ユーザがリモートDBのオブジェクトにアクセスすることを可能とする、Oracleのスキーマオブジェクトである。DBLINKを使えば、極めて簡単にリモートにあるデータベースの表にアクセスすることができるだけでなく、2PCといった分散トランザクション管理まで簡単に実現できる。アプリケーションを開発する立場からは極めて便利に思える機能である。
　しかし、だからといって、アプリケーション開発する上でDBLINKを意識する必要がない、ということではない。むしろDBLINKに依存したアプリケーションであることを意識し、背後にあるアーキテクチャや制約事項を正確に理解してアプリケーションを作る必要があるだろう。
　ここでは一例として、DBLINKでリモートデータベースにあるLOB列（以下リモートLOBと記す）を利用する際の制約について考えてみたい。マニュアルによると、サポートされていない形式で使うとORA-22992が発生することがあると記載されている。
データベースSecureFilesおよびラージ・オブジェクト開発者ガイド　19c

2.7.1 LOB列のルール
...
　Oracle DatabaseによるリモートLOBに対するサポートは制限されているため、リモートLOBをサポートされていない形式で使用すると、ORA-22992エラーが発生する可能性があります。

　実際に制限されている内容は5.1 リモートLOBの操作に記載されている。ただ、これを読んだだけではよくわからないため、実際に検証してみたいと思う。

２．リモートLOBの検証

　リモートLOBの動作検証をしていく。環境は手元のOracleVM上に構成した19.11.0である。

(1)テストデータの準備

　まず、BLOBを含むテストテーブルを作成し、テストデータを入れる。BLOBは乱数で生成した80文字をRAWに変換したものを使う。

SQL> create table t1 (id number, dt blob );
Table created.
SQL> alter table t1 add constraint pk_t1 primary key ( id );
Table altered.
SQL> insert into t1 values(1,utl_raw.cast_to_raw(dbms_random.string('u',80)));1 row created.
SQL> commit;
Commit complete.
SQL>
SQL> col dt for a40
SQL> select * from t1;
        ID DT
---------- ----------------------------------------
         1 525A44454C49524B46524E42434949594A5A4655
           48544842424A4754434B444C4449534341584459
           4D5051574953445149514C4A4F4C5A53494A584E
           5456574A41464443474D5856505651504C4B524B
SQL>

(2)リモートLOBへのビュー、シノニム、MVIEW

　このテーブルに対してビューを作成する。当たり前だが、特に問題なく作成、参照できる。

SQL> create view v_t1 as select * from t1;
View created.
SQL> select * from v_t1;
        ID DT
---------- ----------------------------------------
         1 525A44454C49524B46524E42434949594A5A4655
           48544842424A4754434B444C4449534341584459
           4D5051574953445149514C4A4F4C5A53494A584E
           5456574A41464443474D5856505651504C4B524B
SQL>

　次にDBLINKを作成する。自己参照としているが、DBLINKの挙動を確認する上では問題ないだろう。

SQL> create database link d_k5 using 'k5';
Database link created.
SQL>

　DBLINK経由でLOBにアクセスしてみる。こちらも正常にアクセスできることを確認できた。

SQL> select * from t1@d_k5;

        ID DT
---------- ----------------------------------------
         1 525A44454C49524B46524E42434949594A5A4655
           48544842424A4754434B444C4449534341584459
           4D5051574953445149514C4A4F4C5A53494A584E
           5456574A41464443474D5856505651504C4B524B
SQL>

　さて、このSQLを使って、DBLINK越しにビューを作成しようとすると、以下のようにORA-22992が発生する。LOB列を含むリモートビューは作成できない（もちろんselectでLOB列を除けば、ビューの作成は可能である）。

SQL> create view v_k5t1 as select * from t1@d_k5;
create view v_k5t1 as select * from t1@d_k5
                             *
ERROR at line 1:
ORA-22992: cannot use LOB locators selected from remote tables
SQL> !oerr ora 22992
22992, 00000, "cannot use LOB locators selected from remote tables"
// *Cause:  A remote LOB column cannot be referenced.
// *Action:  Remove references to LOBs in remote tables.
SQL>

　ビューではなく、シノニムであれば問題ない。実質これはDBLINK名を直接指定しているのと同じだからだろう。

SQL> create synonym s_k5t1 for f.t1@d_k5;
Synonym created.
SQL> select * from s_k5t1;
        ID DT
---------- ----------------------------------------
         1 525A44454C49524B46524E42434949594A5A4655
           48544842424A4754434B444C4449534341584459
           4D5051574953445149514C4A4F4C5A53494A584E
           5456574A41464443474D5856505651504C4B524B
SQL>

　MVIEWはLOB列が含まれていても問題ない。LOBがローカル側に実体化されているので、通常のLOBと同じように扱うことができる。

SQL> create materialized view m_k5t1 as select * from f.t1@d_k5;
Materialized view created.
SQL>
        ID DT
---------- ----------------------------------------
         1 525A44454C49524B46524E42434949594A5A4655
           48544842424A4754434B444C4449534341584459
           4D5051574953445149514C4A4F4C5A53494A584E
           5456574A41464443474D5856505651504C4B524B
SQL>

(3)リモートLOBへのDBMS_LOB関数

　次にdbms_lobを使った動作を確認する。dbms_lob.getlengthでローカルのLOBカラム長を確認する。当然これは問題ない。

SQL> select id,dbms_lob.getlength(dt) from t1;
        ID DBMS_LOB.GETLENGTH(DT)
---------- ----------------------
         1                     80
SQL>

　同じことをDBLINK経由で取得したLOB列に対して実行すると、以下のようにORA-22992のエラーが発生する。

SQL> select id,dbms_lob.getlength(dt) from t1@d_k5;
select id,dbms_lob.getlength(dt) from t1@d_k5
                             *
ERROR at line 1:
ORA-22992: cannot use LOB locators selected from remote tables
SQL>

　この場合は、以下のようにdbms_lobをリモート側で実行するよう指定することで回避できる。このように関数にDBLINKを指定することができることは知らなかった。

SQL> select id,dbms_lob.getlength@d_k5(dt) from t1@d_k5;
        ID DBMS_LOB.GETLENGTH@D_K5(DT)
---------- ---------------------------
         1                          80
SQL>

　LOB長に関していえば、SQL関数がサポートされているから、下記のように書くこともできる。dbms_lobを使わなくてよいのは楽である。

SQL> select id,length(dt) from t1@d_k5;
        ID LENGTH(DT)
---------- ----------
         1         80
SQL>

(4)リモートLOBからのCTASの動作

　DBLINK経由でCTASでテーブルを作成する。これは当然問題ない。

SQL> create table t2 as select id,dt from f.t1@d_k5;
Table created.
SQL> select * from t2;
        ID DT
---------- ----------------------------------------
         1 525A44454C49524B46524E42434949594A5A4655
           48544842424A4754434B444C4449534341584459
           4D5051574953445149514C4A4F4C5A53494A584E
           5456574A41464443474D5856505651504C4B524B
SQL>

　dbms_lob.substrでBLOBの一部を抜き出したテーブルを作成することも問題ない。オフセット1（先頭）から10バイト取得できていることが確認できる。

SQL> create table t3 as select id,dbms_lob.substr(dt,10,1) dt from f.t1;
Table created.
SQL> select * from t3;
        ID DT
---------- ----------------------------------------
         1 525A44454C49524B4652
SQL>

　しかし、これをDBLINK経由で行うとORA-22992エラーとなる。試していないが、おそらくINSERT INTO SELECTでも同じエラーになるだろう。

SQL> create table t4 as select id,dbms_lob.substr(dt,10,1) dt from f.t1@d_k5;
create table t4 as select id,dbms_lob.substr(dt,10,1) dt from f.t1@d_k5
                                             *
ERROR at line 1:
ORA-22992: cannot use LOB locators selected from remote tables
SQL>

　この場合はdbms_lobをリモート側で実行するよう指定しても、以下の通りエラーは回避できない。dbms_lob.substr関数のようにLOBを返却する関数はサポートされていないためである。

SQL> create table t4 as select id,dbms_lob.substr@d_k5(dt,10,1) dt from f.t1@d_k5;
create table t4 as select id,dbms_lob.substr@d_k5(dt,10,1) dt from f.t1@d_k5
                             *
ERROR at line 1:
ORA-06553: PLS-564: lob arguments are not permitted in calls to remote server
SQL>

　dbms_lobではなく、substr関数を使えば、エラーは回避できる。ただ、この場合はBLOBがVARCHAR2に変換されてしまっている。DBLINK経由で関数がLOBを返却できないためだろう。

SQL> create table t4 as select id,substr(dt,1,10) dt from f.t1@d_k5;
Table created.
SQL> select * from t4;
        ID DT
---------- ----------------------------------------
         1 RZDELIRKFR
SQL>
SQL> desc t4
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER
 DT                                                 VARCHAR2(40)
SQL>

３．まとめ

　DBLINK経由でリモートLOBを利用する際の制約について、マニュアルに記載されている事項のいくつかを実機にて動作確認した。その結果、VIEWの作成や一部のDBMS_LOB関数は、ORA-22992が発生するため利用できないことがわかった。
　本質的にはOracleのLOBはLOBロケータを介して実体にアクセスするため、取得されたLOBロケータを関数が処理する際、その実体がリモートにあるとエラーになるのだと思われる。スカラー型を返却するDBMS_LOB関数にDBLINK名を指定することで、関数の実行自体がリモートで実行され、このエラーが回避できる。ただ、これはdbms_lob.getlengthなどLOB値からスカラー値を返却する関数の話で、LOB値を返却するdbms_lob.substr関数ではリモートLOBがサポートされておらず、エラーは回避できない。
　表のDBLINK名はシノニムやビューで隠蔽してAPから意識させないようにすることはあるだろう。しかし、LOB列が含まれている表に対しては、このような制約があることから、AP開発上はリモートであることを意識しながら行う必要があるだろう。MVIEW等でローカルにレプリカを作成すればこの制約を受けないため、あまり気にする必要はないかもしれないが、DBLINKで直接リモートLOBを使う場合は、実際にやりたいことができるかを事前に十分検証しておく必要があるだろう。
　そして何よりも基本的な考え方として、このようなAP開発上の複雑性を回避するために、DBLINKの利用は必要最低限に留めるのが良いだろうと、改めて考えさせられた。

明日は12/23、再びHiroshi Sekiguchiさんですね。解答が楽しみです。