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

Use a DBLink to modify data in a remote database

Last Updated：2023-08-04 08:22:14 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 OceanBase databases in Oracle mode or Oracle databases.

Limitations

DBLinks are not supported by OceanBase Database in MySQL mode.
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, such as the ip, port, user_name, tenant_name, and pass_word, of the local database 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.
DBLink fully supports data writes from the local database to the objects in the remote database by using ODP. However, the reverse link feature does not support the access from the remote database to the objects in the local database by using ODP.

Prerequisites

You have created a DBLink. For more information about how to create a DBLink, see Create a DBLink.

Considerations

When you use a DBLink to write data to a remote database, OceanBase Database automatically enables local and remote 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.

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

Syntax for inserting data into a table by using a DBLink:

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

Parameters:

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 the data will 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.

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

Syntax for updating the data of a table by using a DBLink:

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

Parameters:

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 columns to be updated. The value after the equal sign (=) is the new value.
WHERE condition: 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.

Example of changing the value of the C1 column to 3 for the t2 table in the remote database 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 rows in set

Updating the data of a table in a remote database by using a DBLink is similar 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

Syntax for replacing the data of a table by using a 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);

Parameters:

MERGE INTO table_name@dblink_name alias1: the name and alias of the target table in the remote database. dblink_name is name of the DBLink.
USING (table|view|sub_query) alias2: the name and alias of the source table, view, or subquery.
ON (join condition): the condition based on which the specified operation is performed.
WHEN MATCHED THEN UPDATE table_name SET col1 = col_val1 , col2 = col2_val: When the condition is met, the UPDATE statement is executed.
WHEN NOT MATCHED THEN INSERT (column_list) VALUES (column_values);: 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 target table, data is inserted into the target table. Otherwise, the existing record in the target table is updated.

Example of replacing data of the t3 table in the remote database by using 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.

Delete data

Syntax for deleting the data of a table by using a DBLink:

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

Parameters:

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.

Example of deleting the row where C2 = 7 from the t5 table in the remote database 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 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.

More information

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