PolarDB-O provides the database link feature. You can use the database link feature to query data across databases. This topic describes how to query data across databases by using a database link.

Prerequisites

  • A source cluster and a destination cluster are created. For more information, see Create a PolarDB-O cluster.
    Note PolarDB-O database links support the following connection modes:
    • The source database is a PolarDB-O cluster, and the destination database is a PolarDB-O cluster.
    • The source database is a PolarDB-O cluster, and the destination database is a user-created Oracle database hosted on ECS.
  • Databases are created in the source and the destination clusters. For more information, see Create a database.
  • Tables are created in the destination database.

Notes

You can only query the data in the destination cluster from the source cluster. You cannot query the data in the source cluster from the destination cluster.

Procedure

  1. Create a database link.
    PolarDB-O provides the following two methods to create database links:
  2. Connect to the source cluster. For more information, see Connect to a PolarDB-O cluster.
  3. Query data across databases from the source cluster.

    Run the following statement:

    SELECT * FROM <dbname>@<dblinkname>;
    • <dbname>: the table in the database of the destination cluster.
      Note The table must be in the destination database to which the database link is connected.
    • <dblinkname>: the name of the database link.

    You can view the name of the database link and the destination database to which the database link is connected in the console, as shown in the following figure.

    dblink

Examples

Create a table named test in the destination database for testing, and insert test data into the table. You can run the following command:

CREATE TABLE test(id int);
INSERT INTO test VALUES(1);

Connect to the source database and run the following query command:

SELECT * FROM test@dblinkname;

The following result is returned:

 id
----
  1
(1 row)