Use a DBLink to access data in a remote database

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

Prerequisites

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

Access data of tables in a remote database

Execute the SELECT... FROM table name@DBLink name statement to access data in a table in the remote database. Sample SQL statement:

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]

Parameters:

• select_expr_list: 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: the tables to be queried in the remote database. Separate the tables with commas (,).

• where_conditions: 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> SELECT * FROM tbl1@my_link;

More information

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

• Create a DBLink

• View existing DBLinks

• Use a DBLink to modify data in a remote database

• Drop a DBLink