Use a DBLink in queries

The DBLink feature of OceanBase Database enables cross-database access, allowing you to access remote databases from the local database.

Prerequisites

• The account used to create a DBLink must be a superuser. For more information about how to create a superuser, see Create and manage an account.
• The remote database tenant must configure whitelists based on the local access address. For more information, see Set allowlist groups.
• You have obtained the remote database connection information. For more information, see Database connection overview.

Create a DBLink

After you create a DBLink, you can access objects such as tables, views, and synonyms in the remote database through the DBLink.

Create a DBLink between OceanBase Databases

To create a DBLink, you must specify the DBLink name and provide the account name, tenant ID, password, access address, and access type of the remote database. The write data feature of DBLink includes a reverse link feature, which is used to access objects such as tables, views, and synonyms in the local database from the remote database. If you want to use the reverse link feature, you must provide the account name, tenant ID, password, and access address of the local database when you create the DBLink.

In Oracle compatible mode, the SQL syntax for creating a DBLink between Oracle-compatible tenants of OceanBase Database is as follows:

CREATE DATABASE LINK dblink_name CONNECT TO user@tenantid IDENTIFIED BY remote_password [OB] HOST 'ip:port'
    [MY_NAME local_user@local_tenantid IDENTIFIED BY local_password HOST 'local_ip:local_port'];

The parameters are described as follows:

• dblink_name: the name of the DBLink, which is 128 characters or less in length.

• user: the username of the remote OceanBase Database.

• tenantid: the tenant ID of the remote OceanBase database.

• remote_password: the login password of the remote OceanBase Database account. If the password contains special characters (non-alphanumeric) such as @#!, enclose the password in double quotation marks to avoid a syntax error.

• OB: Optional. Specifies the type of the remote database to access. If you do not specify this parameter, the default value is OB, which specifies the OceanBase database.

• ip:port: specifies the access address of the remote OceanBase tenant.

  If the access address is a private IP address, the network between the local database and the specified tenant must be available.

• [MY_NAME local_user@local_tenantid IDENTIFIED BY local_password HOST 'local_ip:local_port']: Optional. The account name, tenant ID, password, and access address of the local database. If you need to use the reverse link feature for writing data to the DBLink, you need to provide this information.

  • local_user: the username of the local database.

  • local_tenantid: the tenant ID of the local database.

  • local_password: the login password of the local database account. If the password contains special characters (non-alphanumeric) such as @ ! #, enclose the password in double quotation marks to avoid a syntax error.

  • local_ip:local_port: the access address of the local OceanBase Database tenant.

Here is an example:

• Create a DBLink that connects to the remote OceanBase Database.

  obclient> CREATE DATABASE LINK ob_dblink CONNECT TO ob_user@t******** IDENTIFIED BY "******" OB HOST 'xx.xx.xx.xx:1521';
  Query OK, 1 row affected
  

• Create a DBLink named ob_dblink_reverse_link that connects to the remote OceanBase Database and has the reverse link feature.

  
  obclient> CREATE DATABASE LINK ob_dblink_reverse_link CONNECT TO ob_user2@t******** IDENTIFIED BY "******" OB HOST  'xx.xx.xx.xx:1521' MY_NAME local_ob_user@oracle IDENTIFIED BY "******" HOST 'xx.xx.xx.xx:1521';
  Query OK, 1 row affected
  

Use a DBLink to query data from a remote database table

The SQL statement is as follows:

SELECT select_expr_list FROM table_name@dblink_name WHERE where_conditions;

The parameters are described as follows:

• select_expr_list: the expressions or column names of the data to be queried. Separate the columns with commas (,). An asterisk (*) represents all columns.

• table_name@dblink_name: the names of the remote tables whose data is to be queried. Separate the tables with commas (,).

• where_conditions: the filter conditions. Only the data that meets the conditions is returned in the query result. This parameter is optional.

Example

To use a DBLink, perform the following steps:

1. Run the following SQL statement to create a DBLink named my_link that connects to the Oracle compatible mode of an OceanBase database.

   CREATE DATABASE LINK my_link CONNECT TO user@t******** IDENTIFIED BY "******" OB HOST 'xxx.xxx.xxx.xxx:1521';
   

2. Run the following SQL statement to query data from the test_tbl1 table in the remote database by using the my_link DBLink.

   SELECT * FROM test_tbl1@my_link;
   

   The return result is as follows:

   +------+------+
   | COL1 | COL2 |
   +------+------+
   |    1 | a1   |
   |    2 | b2   |
   +------+------+
   2 rows in set