Use a DBLink to access data in a remote database

You can use a DBLink to access objects, such as tables, views, synonyms, and sequences, in a remote database.

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, you must install and configure Oracle Call Interface (OCI) 11.2 in the local OceanBase database, For more information about how to install and configure OCI 12.2, see Install and configure OCI.

Access data of tables in a remote database

You can execute the SELECT... FROM table name@DBLink name statement to access data in a table in the remote database. Here is an example:

SELECT select_expr_list
      FROM table_name@dblink_name
      WHERE where_conditions;

select_expr_list:
    table_name.*
    | table_alias_name.*
    | expr [[AS] column_alias_name]

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.*: all columns in the specified table or view.

  • table_alias_name.: the alias of the table or view.

  • expr [[AS] column_alias_name]: the alias of the column or expression that you want to query. AS is optional.

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

For more information about the SQL statements for querying DBLinks, see SIMPLE SELECT.

Example: Use a DBLink named my_link to query data of the tbl1 table in the remote database:

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

References

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

• Create a DBLink

• Query existing DBLinks

• Use a DBLink to modify data in a remote database

• Drop a DBLink