Use a DBLink to modify data in a remote database|V4.3.1| docs|Distributed Database

Use a DBLink to modify data in a remote database

Last Updated：2024-06-28 05:30:30 Updated

After you create a DBLink, you can use the DBLink to modify data in the remote database. DBLinks allow you to write data to Oracle Database or the Oracle mode of OceanBase Database.

Limitations

When you use a DBLink for write operations, the version of the remote database must be V4.1.0 or later if it is an OceanBase database, and be 11g or later if it is an Oracle database.
The data write feature of DBLink supports reverse links. The remote database can access objects, such as tables, views, and synonyms, in the local database through a reverse link. To use the reverse link feature, you must provide information about the local database, such as the ip, port, user_name, tenant_name, and pass_word, when you create a DBLink. For more information, see Create a DBLink.
The reverse link feature supports only the access between two OceanBase databases in Oracle mode.

Prerequisites

You have created a DBLink. For more information about how to create a DBLink, see Create a DBLink.
When you use a DBLink to access a remote Oracle database, if the local OceanBase database is of V4.2.1 or later, you must install and configure Oracle Call Interface (OCI) 12.2 on all OBServer nodes in the cluster. If the local OceanBase database was upgraded from an earlier version to V4.2.1 or later, you must upgrade the originally configured OCI 11.2 to OCI 12.2.

For more information about how to install and configure OCI 12.2, see Install and configure OCI.

Considerations

When you use a DBLink to write data to a remote database, OceanBase Database automatically enables local and remote XA transactions.

For more information about XA transactions, see XA transactions.
After you write data to the remote database by using a DBLink, you must use the COMMIT or ROLLBACK statement to commit or roll back the local and remote XA transactions.

For more information about transaction control statements, see Overview.
When you use a DBLink to write data to a remote database, you cannot enable auto commit by setting the autocommit variable to True. In addition, when an XA transaction is enabled, the system forcibly sets autocommit to False for the current session and restores autocommit to its original value only after the XA transaction is committed or rolled back.

The system variable autocommit specifies whether to enable auto commit of transactions. The default value is True. For more information about the system variable autocommit, see autocommit.
If an XA transaction is expected to take a long time, you can use the set ob_trx_timeout = 1000000000; statement to increase the transaction timeout period in advance to prevent unexpected errors due to the timeout of the XA transaction.

The system variable ob_trx_timeout specifies the timeout period of transactions. The default value is 86400000000, in microseconds. For more information about the system variable ob_trx_timeout, see ob_trx_timeout.
After you call a remote user-defined function (UDF) over a DBLink, in SQL, you must use the COMMIT statement to commit local and remote XA transactions, and you cannot set autocommit to True to enable auto commit of transactions. In PL, you can set autocommit to True to enable auto commit of transactions.

Modify data in a remote database

You can use a DBLink to modify the data in a remote database by using the following types of DML statements: INSERT, UPDATE, MERGE INTO, and DELETE.

Insert data

The syntax for inserting data into a table by using a DBLink is as follows:

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

where:

In table_name@dblink_name, table_name specifies the name of the target table in the remote database, and dblink_name specifies the DBLink name.
list_of_columns specifies the table columns into which the data will be inserted.
list_of_values specifies the values of the columns specified by the list_of_columns parameter. The values and columns must be fully matched.

Here is an example of inserting a row (11,11) into the t1 table in the remote database connected through a DBLink:

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

Inserting data into a table in a remote database by using a DBLink is similar to directly inserting data into a table. The only difference is that you need to suffix @dblink_name to the name of the target table in the statement. For more information about how to insert data into a table, see Insert data.

For more information about the INSERT statement, see INSERT.

Insert data of a local table into a remote table

The syntax for writing data from a local table to a remote table by using a DBLink is as follows:

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 ...]

The parameters are described as follows:

table_name@dblink_name:
- table_name specifies the name of the remote table where data is to be inserted.
- dblink_name specifies the name of the DBLink created at the local database and directed to the remote database.
list_of_columns: the list of columns in the target table where data is to be inserted. This parameter is optional. If you do not specify this parameter, the SQL statement matches the columns of the source table against those of the target table in order and inserts data into all columns of the target table that are specified in the SELECT clause.
table_name_local: the name of the source table in the local database.
table_name_dblink@dblink_name:
- table_name_dblink specifies the name of the source table in the remote database.
- dblink_name_select specifies the name of the DBLink used to access the source table in the remote database.
WHERE query_condition: the conditions in the SELECT statement. query_condition specifies the rows of the source table to be inserted into the destination table. This parameter is optional.

Here is an example:

Query data of the local table tbl1.

obclient [SYS]> SELECT * FROM tbl1;

The return result is as follows:

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

Query data of the a_tbl1 table in remote database A.
```
obclient [SYS]> SELECT * FROM a_tbl1@ob_dblink_a;
```
The return result is as follows:
```
Empty set
```

Query data of the b_tbl1 table in remote database B.

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

The return result is as follows:

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

Access the remote table b_tbl1 over the ob_dblink_b DBLink, and select records that match the col1 and col2 columns of the b_tbl1 table from the local table tbl1. Then insert the selected values of the col1 and col2 columns into the remote table a_tbl1 over the ob_dblink_a DBLink.
```
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;
```
The return result is as follows:
```
Query OK, 1 row affected
```

Query data of the a_tbl1 table in remote database A again.

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

The return result is as follows:

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

Update data

The syntax for updating the data of a table by using a DBLink is as follows:

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

where:

In table_name@dblink_name, table_name specifies the name of the target table in the remote database, and dblink_name specifies the DBLink name.
column_name = value [, column_name = value] specifies the columns to be updated. The value after the equal sign (=) is the new value.
WHERE condition specifies the condition that must be met by the rows to be updated. If no condition is specified, all rows of the corresponding columns are updated.

Here is an example of changing the value of the C1 column to 3 for the t2 table in the remote database connected through a DBLink:

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

Updating the data of a table in a remote database by using a DBLink is similar to directly updating the data of a table. The only difference is that you need to suffix @dblink_name to the name of the target table in the statement. For more information about how to update the data of a table, see Update data.

For more information about the UPDATE statement, see UPDATE.

Update data of a local table to a remote table

Here is an example:

Query data of the local table tbl1.

obclient [SYS]> SELECT * FROM tbl1;

The return result is as follows:

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

Query data of the b_tbl1 table in remote database B.

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

The return result is as follows:

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

Update the remote table b_tbl1 over the ob_dblink_b DBLink to change the values of the col2 column in the b_tbl1 table to values of the col2 column in the local table tbl1. Only the rows in the b_tbl1 table whose col1 column values match those of the tbl1 table are updated.

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);

The return result is as follows:

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

Query data of the b_tbl1 table in remote database B again.

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

The return result is as follows:

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

Replace data

The syntax for replacing the data of a table by using a DBLink is as follows:

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);

where:

MERGE INTO table_name@dblink_name alias1 specifies the name and alias of the target table in the remote database, where dblink_name is the name of the DBLink.
USING (table|view|sub_query) alias2 specifies the name and alias of the source table, view, or subquery.
ON (join condition) specifies the condition based on which the specified operation is performed.
WHEN MATCHED THEN UPDATE table_name SET col1 = col_val1 , col2 = col2_val specifies that when the condition is met, the UPDATE statement is executed.
WHEN NOT MATCHED THEN INSERT (column_list) VALUES (column_values); specifies that when the condition is not met, the INSERT statement is executed.

When you use a MERGE INTO statement to replace data, if the record in the source table does not exist in the destination table, data is inserted into the destination table. Otherwise, the existing record in the destination table is updated.

Here is an example of replacing data of the t3 table in the remote database connected through a DBLink:

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

Replacing the data of a table in a remote database by using a DBLink is similar to directly replacing the data of a table. The only difference is that you need to suffix @dblink_name to the name of the target table in the statement. For more information about how to replace the data of a table, see Replace data.

For more information about the MERGE INTO statement, see MERGE INTO.

Replace data of a remote table with data of a local table

Here is an example:

Query data of the local table tbl1.

obclient [SYS]> SELECT * FROM tbl1;

The return result is as follows:

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

Query data of the b_tbl1 table in remote database B.

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

The return result is as follows:

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

Update the remote table b_tbl1 over the ob_dblink_b DBLink to change the values of the col2 column in the b_tbl1 table to values of the col2 column in the local table tbl1. Only the rows in the b_tbl1 table whose col1 column values match those of the tbl1 table are updated. If the source table tbl1 contains rows whose col1 column values do not match those of the tbl1 table, the INSERT operation is executed to insert new rows into the target table b_tbl1. In this case, values of the col1 and col2 columns of the new rows in the b_tbl1 table come from corresponding columns in the source table 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);
```
The return result is as follows:
```
Query OK, 3 rows affected
```

Query data of the b_tbl1 table in remote database B again.

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

The return result is as follows:

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

Delete data

The syntax for deleting data from a table by using a DBLink is as follows:

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

where:

In table_name@dblink_name, table_name specifies the name of the target table in the remote database, and dblink_name specifies the DBLink name.
WHERE condition specifies the condition that must be met by the data to be deleted. If no condition is specified, all data in the table is deleted.

Here is an example of deleting the row where C2 = 7 from the t5 table in the remote database connected through a DBLink:

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

Deleting the data of a table in a remote database by using a DBLink is similar to directly deleting the data of a table. The only difference is that you need to suffix @dblink_name to the name of the target table in the statement. For more information about how to delete the data of a table, see Delete data.

For more information about the DELETE statement, see DELETE.

Delete data from a remote table based on data of a local table

Here is an example:

Query data of the local table tbl1.

obclient [SYS]> SELECT * FROM tbl1;

The return result is as follows:

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

Query data of the a_tbl1 table in remote database A.

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

The return result is as follows:

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

Delete rows in the remote table a_tbl1 that match values of the col1 and col2 columns in the local table 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
                );

The return result is as follows:

Query OK, 1 row affected

Query data of the a_tbl1 table in remote database A again.

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

The return result is as follows:

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

References

For more information about operations on DBLinks, see the following topics: