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

Use a DBLink to modify the data in a remote database

Last Updated：2025-01-02 01:58:40 Updated

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

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 by using 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 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, and if OceanBase Database is of a version earlier than V4.2.1, you must upgrade OceanBase Database to V4.2.1 and upgrade 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 eXtended Architecture (XA) transactions.

For more information about XA transactions, see XA transactions.
If you use a DBLink to write data to a remote database in SQL, you must use the COMMIT statement to commit local and remote eXtended Architecture (XA) transactions. You cannot set autocommit to True to enable autocommit of transactions. * If an XA transactions is started when you use a DBLink to write data in SQL, 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.
If you use a DBLink to write data to a remote database in an anonymous block, the transaction is committed in a similar way as a regular transaction. You can set autocommit to True to enable autocommit of transactions. When autocommit is set to False, you need to use the COMMIT statement to explicitly commit transactions.

The system variable autocommit specifies whether to enable autocommit 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.

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

The parameters are described as follows:

table_name@dblink_name: table_name specifies the name of the target table in the remote database. dblink_name specifies the DBLink name.
list_of_columns: the table columns into which data is to be inserted.
list_of_values: 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 by using 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.

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

The parameters are described as follows:

table_name@dblink_name: table_name specifies the name of the target table in the remote database. dblink_name specifies the DBLink name.
column_name = value [, column_name = value]: the column to be updated. The value after the equal sign (=) is the new value.
WHERE condition: the condition for updating rows. 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 by using 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 row 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.

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

The parameters are described as follows:

MERGE INTO table_name@dblink_name alias1: the name and alias of the target table in the remote database. dblink_name is the name of the DBLink.
USING (table|view|sub_query) alias2: the source table (view, subquery) and its alias.
ON (join condition): the judgment condition.
WHEN MATCHED THEN UPDATE table_name SET col1 = col_val1 , col2 = col2_val: specifies to execute the UPDATE statement when the condition is met.
WHEN NOT MATCHED THEN INSERT (column_list) VALUES (column_values);: specifies to execute the INSERT statement when the specified condition is not met.

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

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

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

obclient> SELECT * FROM t4;
+------+------+
| C1   | C2   |
+------+------+
|    5 |    5 |
|    6 |    6 |
+------+------+
2 rows 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 rows 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.

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

The parameters are described as follows:

table_name@dblink_name: table_name specifies the name of the target table in the remote database. dblink_name specifies the DBLink name.
WHERE condition: 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 by using 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 rows 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.

Support for savepoints

Note

Savepoints are supported for DBLink writes. You can roll back a transaction to a specified savepoint.

Here is an example:

Assume that an empty table named tbl1 exists in a remote database. The following procedure shows how to roll back a transaction to a specified savepoint.

Insert Row 1 into the tbl1 table.
```
INSERT INTO tbl1@dblink_o2 VALUES(1);
```
Create a savepoint named sp1.
```
SAVEPOINT sp1;
```
Insert Row 2 into the tbl1 table.
```
INSERT INTO tbl1@dblink_o2 VALUES(2);
```
Create a savepoint named sp2.
```
SAVEPOINT sp2;
```
Roll back the transaction to sp1.
```
ROLLBACK TO sp1;
```
Insert Row 3 into the tbl1 table.
```
INSERT INTO tbl1@dblink_o2 VALUES(3);
```
Commit the transaction.
```
COMMIT;
```

Query the data in the tbl1 table.

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

The return result is as follows:

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

References

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