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. After a DBLink is created, you can use it to access remote database objects, including tables, views, and synonyms.

Prerequisites

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

Query the data of a table in a remote database

The SQL syntax is as follows:

SELECT select_expr_list FROM table_name@dblink_name WHERE where_conditions;

where

• select_expr_list specifies the expressions or column names to be queried. Separate the column names with commas (,). An asterisk (*) indicates all columns.

• table_name@dblink_name specifies the tables to be queried in the remote database. Separate the tables with commas (,).

• where_conditions specifies the filter condition, which is optional. Only the data that meets the condition is returned.

Examples

To use a DBLink for data query, perform the following steps:

1. Create a DBLink for connecting to a remote OceanBase database in Oracle mode. The DBLink name is my_link, and the remote tenant is oracle001.

   CREATE DATABASE LINK my_link CONNECT TO sys@oracle001 IDENTIFIED BY "******" OB HOST 'xxx.xxx.xxx.xxx:2881';
   

2. Query the data of the test_tbl1 table in the remote database through the DBLink named my_link.

   SELECT * FROM test_tbl1@my_link;
   

   The result is as follows:

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

References

• For more information about how to create a DBLink, see Create a DBLink.

• For more information about how to use a DBLink, see Use a DBLink to access data in a remote database.

• For more information about how to use the SELECT statement, see SELECT statement.