DBLinkを使用してリモートデータベースのデータを変更する

DBLinkを作成後、それを使用してリモートデータベース内のデータを変更できます。現在、DBLinkを通じてOceanBaseデータベースのOracleモードデータベースおよびOracleデータベースへのデータ書き込みがサポートされています。

使用上の制限

• DBLinkの書き込み機能を使用する場合、リモートデータベースがOceanBaseデータベースの場合は、リモート側のOceanBaseデータベースのバージョンがV4.1.0以上である必要があります。リモートデータベースがOracleデータベースの場合は、Oracle 11g以上のバージョンを使用することを推奨します。

• DBLinkのデータ書き込み機能には、リバースリンク機能があります。この機能は主に、リモートデータベースからローカルデータベースのオブジェクト（テーブル、ビュー、シノニムなど）にアクセスするために使用されます。DBLinkのデータ書き込み機能でリバースリンク機能を使用する必要がある場合は、DBLinkを作成する際に、ローカルデータベースのip、port、user_name、tenant_name、pass_wordなどの情報を提供する必要があります。具体的な操作手順および詳細な説明については、DBLinkの作成を参照してください。

• 現在、リバースリンク機能はOceanBaseデータベースのOracleモード間のアクセスのみをサポートしており、OceanBaseデータベースのOracleモードからOracleデータベースへのアクセスは現在サポートされていません。

前提条件

• DBLinkが作成済みであること。DBLinkの作成手順については、DBLinkの作成を参照してください。

• OceanBase V4.2.1以降のバージョンでは、DBLink機能を使用してリモートのOracleデータベースにアクセスする場合、クラスタ内のすべてのOBServerノードにOCI-12.2をインストールし、設定する必要があります。また、OceanBase V4.2.1未満からV4.2.1以上にバージョンアップグレードする場合も、既に設定されているOCI-11.2をOCI-12.2に再設定する必要があります。

  OCI-12.2のインストールと設定の詳細な手順については、OCIのインストールと設定を参照してください。

注意点

• DBLinkを使用してリモートデータベースにデータを書き込む場合、OceanBaseデータベースはローカルおよびリモートのXAトランザクションを自動的に開始します。ユーザーが手動で開始する必要はありません。

  XAトランザクションの詳細については、XAトランザクションを参照してください。

• DBLinkを使用してリモートデータベースにデータを書き込む操作が完了した後、ローカルおよびリモートのXAトランザクションをコミットまたはロールバックするために、COMMIT または ROLLBACK ステートメントを手動で実行する必要があります。

  より詳細なトランザクション制御ステートメントの説明については、トランザクション管理の概要を参照してください。

• DBLinkを使用してリモートデータベースにデータを書き込む際、autocommit = True を設定してトランザクションの自動コミットを有効にすることは現在サポートされていません。また、DBLinkを使用したデータ書き込み操作でXAトランザクションが開始されると、システムはそのセッションの autocommit 値を強制的に False に設定します。XAトランザクションがコミットまたはロールバックされるまで、autocommit の元の値は復元されません。

  システム変数 autocommit は、トランザクションの自動コミットを有効にするかどうかを設定します。デフォルト値は True です。システム変数 autocommit の詳細については、autocommitを参照してください。

• XAトランザクションが想定以上に時間を要する場合、トランザクションのタイムアウトによる予期しないエラーを防ぐために、set ob_trx_timeout = 1000000000; ステートメントを使用して、事前にトランザクションのタイムアウト時間を延長できます。

  システム変数 ob_trx_timeout は、トランザクションのタイムアウト時間を設定します。デフォルト値は 86400000000 で、単位はマイクロ秒です。システム変数 ob_trx_timeout の詳細については、ob_trx_timeoutを参照してください。

• DBLinkを使用してリモートのUDF（ユーザー定義関数）を呼び出した後、SQLでは COMMIT ステートメントを明示的に使用して、ローカルおよびリモートのXAトランザクションをコミットする必要があります。autocommit = True を設定してトランザクションの自動コミットを有効にすることは現在サポートされていません。PLでは、autocommit = True を設定することで、トランザクションの自動コミットを有効にできます。

リモートデータベース内のデータの変更

DBLinkを使用してリモートデータベース内のデータを変更する操作には、データの挿入、更新、置換、削除などが含まれます。主に INSERT、UPDATE、MERGE INTO、DELETE の4種類のDMLステートメントによって実現されます。

データの挿入

DBLinkを使用してテーブルにデータを挿入する基本構文は以下のとおりです：

obclient> INSERT INTO table_name@dblink_name (list_of_columns) VALUES (list_of_values);

関連パラメータの説明は以下のとおりです：

• table_name@dblink_name：table_name は変更対象のリモートデータベースのテーブル名を表します。dblink_name はDBLink名を表します。

• list_of_columns：データを挿入する列を指定します。

• list_of_values：list_of_columns で指定した列に対応する値です。値は列と1対1で対応している必要があります。

DBLinkを使用して、リモートデータベースのテーブル t1 に行 (11,11) を挿入する例は以下のとおりです：

obclient> SELECT * FROM t1@ob_dblink;
+------+------+
| C1   | C2   |
+------+------+
|    1 |    1 |
+------+------+
1 row in set

obclient> INSERT INTO t1@ob_dblink VALUES (11,11);
Query OK, 1 row affected

obclient> SELECT * FROM t1@ob_dblink;
+------+------+
| C1   | C2   |
+------+------+
|    1 |    1 |
|   11 |   11 |
+------+------+
2 rows in set

DBLinkを使用してリモートデータベースのテーブルにデータを挿入する操作は、直接テーブルにデータを挿入する操作と似ています。ステートメント内の操作対象のテーブル名の後に @dblink_name サフィックスを追加するだけで済みます。テーブルへのデータ挿入の詳細な操作については、データの挿入を参照してください。

INSERT ステートメントの詳細な使用方法と説明については、INSERTを参照してください。

ローカルテーブルデータをリモートテーブルに挿入する

DBLinkを使用してローカルテーブルデータをリモートテーブルに書き込む基本構文は以下のとおりです：

INSERT INTO table_name@dblink_name[(list_of_columns)] SELECT * FROM table_name_list [WHERE query_condition];

table_name_list:
    table_name_local [, table_name_local ...] [, table_name_dblink@dblink_name_select ...]

関連パラメータの説明は以下のとおりです：

• table_name@dblink_name：ターゲットテーブル名とDBLink名。

  • table_name：データを挿入する対象のリモートテーブル名を表します。
  • dblink_name：ローカルデータベースで作成された、リモートデータベースを指すデータベースリンク(DBLink)の名前を表します。

• list_of_columns：オプションです。ターゲットテーブルに挿入するデータの列のリストを指定します。省略した場合、SQLステートメントはソーステーブルの列順序とターゲットテーブルの列をマッチングし、ターゲットテーブルのSELECT句で選択されたすべての列を含みます。

• table_name_local：ローカルデータベース内のソーステーブル名を表します。

• table_name_dblink@dblink_name：DBLinkを介してリモートデータベースで選択されたソーステーブル。

  • table_name_dblink：リモートソーステーブル名を表します。
  • dblink_name_select：リモートソーステーブルにアクセスするために使用されるDBLinkの名前を表します。

• WHERE query_condition：オプションです。SELECTステートメントの条件部分を表します。query_conditionは、ソーステーブルから選択して挿入操作を実行する行を指定します。

例：

1. ローカルテーブル tbl1 のデータを確認します。

   obclient [SYS]> SELECT * FROM tbl1;
   

   実行結果は次のとおりです：

   +------+------+
   | COL1 | COL2 |
   +------+------+
   |    1 | AAA  |
   |    2 | BBB  |
   |    3 | CCC  |
   +------+------+
   3 rows in set
   

2. リモートデータベースAのテーブル a_tbl1 のデータを確認します。

   obclient [SYS]> SELECT * FROM a_tbl1@ob_dblink_a;
   

   実行結果は次のとおりです：

   Empty set
   

3. リモートデータベースBのテーブル b_tbl1 のデータを確認します。

   obclient [SYS]> SELECT * FROM b_tbl1@ob_dblink_b;
   

   実行結果は次のとおりです：

   +------+------+
   | COL1 | COL2 |
   +------+------+
   |    1 | AA   |
   |    2 | BBB  |
   |    3 | CC   |
   +------+------+
   3 rows in set
   

4. データベースリンク ob_dblink_b を使用してリモートデータベーステーブル b_tbl1 にアクセスし、ローカルテーブル tbl1 から b_tbl1 の col1 および col2 列と一致するレコードを選択します。その後、これらの選択された col1 および col2 値を別のデータベースリンク ob_dblink_a を通じてリモートデータベースのテーブル a_tbl1 に挿入します。

   obclient [SYS]> INSERT INTO a_tbl1@ob_dblink_a
     SELECT t1.col1, t1.col2 FROM tbl1 t1, b_tbl1@ob_dblink_b t2
     WHERE t1.col1 = t2.col1
     AND t1.col2 = t2.col2;
   

   実行結果は次のとおりです：

   Query OK, 1 row affected
   

5. 再度リモートデータベースAのテーブル a_tbl1 のデータを確認します。

   obclient [SYS]> SELECT * FROM a_tbl1@ob_dblink_a;
   

   実行結果は次のとおりです：

   +------+------+
   | COL1 | COL2 |
   +------+------+
   |    2 | BBB  |
   +------+------+
   1 row in set
   

データの更新

DBLinkを使用してテーブルデータを更新する基本構文は次のとおりです：

obclient> UPDATE table_name@dblink_name
SET column_name = value [, column_name = value]...
[WHERE condition];

関連パラメータの説明は以下のとおりです：

• table_name@dblink_name：table_name は変更対象のリモートデータベースのテーブル名を表します。dblink_name はDBLink名を表します。

• column_name = value [, column_name = value]：更新する列を指定します。等号（=）の後ろの value は更新後の値です。

• WHERE condition：条件句は、更新される行が満たす必要がある条件を指定します。条件句がない場合、更新テーブルの対応する列のすべてのレコードが更新されます。

DBLinkを使用して、リモートデータベースのテーブル t2 の C1 列の値を 3 に変更する例を以下に示します：

obclient> SET ob_trx_timeout = 1000000000;
Query OK, 0 rows affected

obclient> SELECT * FROM t2@ob_dblink;
+------+------+
| C1   | C2   |
+------+------+
|    2 |    2 |
+------+------+
1 row in set

obclient> UPDATE t2@ob_dblink SET C1 = 3;
Query OK, 1 row affected

obclient> SELECT * FROM t2@ob_dblink;
+------+------+
| C1   | C2   |
+------+------+
|    3 |    2 |
+------+------+
1 rows in set

DBLinkを使用してリモートデータベースのテーブルデータを更新する操作は、直接テーブルデータを更新する操作と類似しています。ステートメント内で操作対象のテーブル名の後に @dblink_name サフィックスを追加するだけで済みます。テーブルデータの更新に関する詳細な操作については、データの更新を参照してください。

UPDATE ステートメントの詳細な使用方法と説明については、UPDATEを参照してください。

ローカルテーブルデータをリモートテーブルに更新する

例：

1. ローカルテーブル tbl1 のデータを確認します。

   obclient [SYS]> SELECT * FROM tbl1;
   

   実行結果は次のとおりです：

   +------+------+
   | COL1 | COL2 |
   +------+------+
   |    1 | AAA  |
   |    2 | BBB  |
   |    3 | CCC  |
   +------+------+
   3 rows in set
   

2. リモートデータベースBのテーブル b_tbl1 のデータを確認します。

   obclient [SYS]> SELECT * FROM b_tbl1@ob_dblink_b;
   

   実行結果は次のとおりです：

   +------+------+
   | COL1 | COL2 |
   +------+------+
   |    1 | AA   |
   |    2 | BBB  |
   |    3 | CC   |
   +------+------+
   3 rows in set
   

3. DBLink ob_dblink_b を使用してリモートテーブル b_tbl1 を更新し、col2 列の値をローカルテーブル tbl1 の col1 列の値と一致する行の col2 値に変更します。ただし、tbl1 にマッチする col1 値が存在する b_tbl1 の行のみを更新します。

   UPDATE b_tbl1@ob_dblink_b t1
       SET t1.col2 = (SELECT t2.col2
                     FROM tbl1 t2
                     WHERE t2.col1 = t1.col1)
       WHERE EXISTS (SELECT 1
                     FROM tbl1 t2
                     WHERE t2.col1 = t1.col1);
   

   実行結果は次のとおりです：

   Query OK, 3 rows affected
   Rows matched: 0  Changed: 0  Warnings: 0
   

4. 再度リモートデータベースBのテーブル b_tbl1 のデータを確認します。

   obclient [SYS]> SELECT * FROM b_tbl1@ob_dblink_b;
   

   実行結果は次のとおりです：

   +------+------+
   | COL1 | COL2 |
   +------+------+
   |    1 | AAA  |
   |    2 | BBB  |
   |    3 | CCC  |
   +------+------+
   3 rows in set
   

データの置換

DBLinkを使用してテーブルデータを置き換える基本的な構文は以下のとおりです：

obclient> MERGE INTO table_name@dblink_name alias1
USING (table|view|sub_query) alias2
ON (join condition)
WHEN MATCHED THEN
    UPDATE table_name
    SET col1 = col1_val,
        col2 = col2_val
WHEN NOT MATCHED THEN
    INSERT (column_list) VALUES (column_values);

関連パラメータの説明は以下のとおりです：

• MERGE INTO table_name@dblink_name alias1：変更対象のリモートデータベースのテーブル名とエイリアスを指定します。dblink_name はDBLink名を表します。

• USING (table|view|sub_query) alias2：ソーステーブル（ビュー、サブクエリ）とそのエイリアスを指定します。

• ON (join condition)：判断条件を指定します。

• WHEN MATCHED THEN UPDATE table_name SET col1 = col_val1 , col2 = col2_val ：条件が一致した場合、UPDATE ステートメントを実行します。

• WHEN NOT MATCHED THEN INSERT (column_list) VALUES (column_values);：条件が一致しない場合、INSERT ステートメントを実行します。

MERGE INTO ステートメントを使用してデータを置き換える際、ソーステーブルのレコードがターゲットテーブルに存在しない場合は、ターゲットテーブルにデータを挿入します。ソーステーブルのレコードがターゲットテーブルに存在する場合は、レコードを更新します。

DBLinkを使用してリモートデータベースのテーブル t3 のデータを置き換える例は以下のとおりです：

obclient> SELECT * FROM t3@orcl_dblink;
+------+------+
| C1   | C2   |
+------+------+
|    3 |    3 |
|    4 |    4 |
+------+------+
2 row in set

obclient> SELECT * FROM t4;
+------+------+
| C1   | C2   |
+------+------+
|    5 |    5 |
|    6 |    6 |
+------+------+
2 row in set

obclient> MERGE INTO t3@orcl_dblink a
 USING (SELECT C1,C2 FROM t4 ) b
 ON (a.C1 = b.C1)
 WHEN MATCHED THEN
     UPDATE SET a.C2 = b.C2
 WHEN NOT MATCHED THEN
     INSERT (a.C1,a.C2) VALUES(b.C1, b.C2);
Query OK, 2 rows affected

obclient> SELECT * FROM t3@orcl_dblink;
+------+------+
| C1   | C2   |
+------+------+
|    3 |    3 |
|    4 |    4 |
|    5 |    5 |
|    6 |    6 |
+------+------+
4 row in set

DBLinkを使用してリモートデータベースのテーブルデータを置き換える操作は、直接データを置き換える操作と類似しています。ステートメント内で操作対象のテーブル名の後に @dblink_name サフィックスを追加するだけで済みます。テーブルデータの置き換え操作の詳細については、データの置換を参照してください。

MERGE INTO ステートメントの詳細な使用方法と説明については、MERGEを参照してください。

ローカルテーブルのデータでリモートテーブルのデータを置き換える

例：

1. ローカルテーブル tbl1 のデータを確認します。

   obclient [SYS]> SELECT * FROM tbl1;
   

   実行結果は次のとおりです：

   +------+------+
   | COL1 | COL2 |
   +------+------+
   |    1 | AAA  |
   |    2 | BBB  |
   |    3 | CCC  |
   +------+------+
   3 rows in set
   

2. リモートデータベースBのテーブル b_tbl1 のデータを確認します。

   obclient [SYS]> SELECT * FROM b_tbl1@ob_dblink_b;
   

   実行結果は次のとおりです：

   +------+------+
   | COL1 | COL2 |
   +------+------+
   |    1 | AAA  |
   |    2 | B2   |
   |    5 | E5   |
   +------+------+
   3 rows in set
   

3. DBLink ob_dblink_b を使用して、リモートテーブル b_tbl1 を更新し、その col2 列の値を、ローカルテーブル（ソーステーブル）tbl1 の col1 列の値と一致する行の col2 値に変更します。ただし、tbl1 に col1 値が一致する b_tbl1 の行が存在する場合にのみ更新します。ソーステーブル tbl1 に col1 値がリモートテーブル b_tbl1 にマッチしない行がある場合（col1 値に基づく）、INSERT 操作を実行し、ソーステーブル tbl1 の対応する列の値を使用して、新しい行をターゲットテーブル b_tbl1 に挿入します。

   MERGE INTO b_tbl1@ob_dblink_b t1
       USING tbl1 t2
           ON (t2.col1 = t1.col1)
       WHEN MATCHED THEN UPDATE SET t1.col2 = t2.col2
       WHEN NOT MATCHED THEN INSERT (t1.col1, t1.col2) VALUES(t2.col1, t2.col2);
   

   実行結果は次のとおりです：

   Query OK, 3 rows affected
   

4. 再度、リモートデータベースBのテーブル b_tbl1 のデータを確認します。

   obclient [SYS]> SELECT * FROM b_tbl1@ob_dblink_b;
   

   実行結果は次のとおりです：

   +------+------+
   | COL1 | COL2 |
   +------+------+
   |    1 | AAA  |
   |    2 | BBB  |
   |    5 | E5   |
   |    3 | CCC  |
   +------+------+
   4 rows in set
   

データの削除

DBLinkを使用してテーブルデータを削除する基本構文は以下のとおりです：

obclient> DELETE FROM table_name@dblink_name [WHERE condition];

関連パラメータの説明は以下のとおりです：

• table_name@dblink_name：table_name は変更対象のリモートデータベースのテーブル名を表します。dblink_name はDBLink名を表します。

• WHERE condition：WHERE句は、削除するデータが満たす必要がある条件を指定します。条件を指定しない場合は、テーブル全体の削除が実行されます。

DBLinkを使用して、リモートデータベースのテーブル t5 から C2 = 7 のデータ行を削除する例を以下に示します：

obclient> SET ob_trx_timeout = 1000000000;
Query OK, 0 rows affected

obclient> SELECT * FROM t5@orcl_dblink;
+------+------+
| C1   | C2   |
+------+------+
|    7 |    7 |
|    8 |    8 |
+------+------+
2 row in set

obclient> DELETE FROM t5@orcl_dblink WHERE C2 = 7;
Query OK, 1 row affected

obclient> SELECT * FROM t5@orcl_dblink;
+------+------+
| C1   | C2   |
+------+------+
|    8 |    8 |
+------+------+
1 row in set

DBLinkを使用してリモートデータベースのテーブルデータを削除する操作は、直接テーブルデータを削除する操作と類似しています。ステートメント内で操作対象のテーブル名の後に @dblink_name サフィックスを追加するだけで済みます。テーブルデータの削除に関する詳細な操作については、データの削除を参照してください。

DELETE ステートメントの詳細な使用方法と説明については、DELETEを参照してください。

ローカルテーブルのデータに基づいてリモートテーブルのデータを削除する

例：

1. ローカルテーブル tbl1 のデータを確認します。

   obclient [SYS]> SELECT * FROM tbl1;
   

   実行結果は次のとおりです：

   +------+------+
   | COL1 | COL2 |
   +------+------+
   |    1 | AAA  |
   |    2 | BBB  |
   |    3 | CCC  |
   +------+------+
   3 rows in set
   

2. リモートデータベースAのテーブル a_tbl1 のデータを確認します。

   obclient [SYS]> SELECT * FROM a_tbl1@ob_dblink_a;
   

   実行結果は次のとおりです：

   +------+------+
   | COL1 | COL2 |
   +------+------+
   |    2 | BBB  |
   |    1 | AA1  |
   |    2 | BB2  |
   |    3 | CC3  |
   +------+------+
   4 rows in set
   

3. ローカルテーブル tbl1 で col1 列と col2 列の値が一致する行を持つ、リモートテーブル a_tbl1 のすべての行を削除します。

   DELETE FROM a_tbl1@ob_dblink_a t1
       WHERE EXISTS (SELECT 1
                     FROM tbl1 t2
                     WHERE t2.col1 = t1.col1
                     AND t2.col2 = t1.col2
                   );
   

   実行結果は次のとおりです：

   Query OK, 1 row affected
   

4. 再度、リモートデータベースAのテーブル a_tbl1 のデータを確認します。

   obclient [SYS]> SELECT * FROM a_tbl1@ob_dblink_a;
   

   実行結果は次のとおりです：

   +------+------+
   | COL1 | COL2 |
   +------+------+
   |    1 | AA1  |
   |    2 | BB2  |
   |    3 | CC3  |
   +------+------+
   3 rows in set
   

DBLinkはSAVEPOINT機能をサポートしています

例：

リモートデータベースに空のテーブル tbl1 があります。以下のSQLステートメントを使用して、DBLinkがトランザクションを指定したセーブポイントまでロールバックする方法を示します。

1. リモートテーブル tbl1 に行1を挿入します。

   INSERT INTO tbl1@dblink_o2 VALUES(1);
   

2. sp1 という名前のセーブポイントを作成します。

   SAVEPOINT sp1;
   

3. リモートテーブル tbl1 に行2を挿入します。

   INSERT INTO tbl1@dblink_o2 VALUES(2);
   

4. sp2 という名前のセーブポイントを作成します。

   SAVEPOINT sp2;
   

5. 変更を sp1 までロールバックします。

   ROLLBACK TO sp1;
   

6. リモートテーブル tbl1 に行3を挿入します。

   INSERT INTO tbl1@dblink_o2 VALUES(3);
   

7. トランザクションをコミットします。

   COMMIT;
   

8. テーブル tbl1 のデータをクエリします。

   obclient [SYS]> SELECT * FROM tbl1@dblink_o2;
   

   実行結果は次のとおりです：

   +------+
   | C1   |
   +------+
   |    1 |
   |    3 |
   +------+
   2 rows in set
   

関連ドキュメント

DBLinkに関するその他の操作については、以下を参照してください。

• DBLinkの作成

• DBLinkの確認

• DBLinkを使用したリモートデータベースへのアクセス

• DBLinkの削除