CREATE [PUBLIC] DATABASE LINK - PolarDB - Alibaba Cloud Documentation Center

This topic describes how to use CREATE [PUBLIC] DATABASE LINK to create a database link.

Synopsis

-- Connect to a PolarDB for PostgreSQL (Compatible with Oracle) database
CREATE [ PUBLIC ] DATABASE LINK <name>
   CONNECT TO <username> IDENTIFIED BY "<password>"
  USING ( channel_name '<channel_name>', dbname '<db_name>' );

-- Connect to an Oracle database
CREATE [ PUBLIC ] DATABASE LINK <name>
   CONNECT TO <username> IDENTIFIED BY "<password>"
  USING '//<channel_name>/<dbname>';

Parameters

Parameter	Description
PUBLIC	Creates the database link that is available to all users. If you do not specify this parameter, the database link is private and is available only to its owner.
name	The name of the database link.
username	The username that is used to connect to the remote database.
password	The password that is used to connect to the remote database.

Usage notes

The database link created is an object and allows for a reference to a table or view in a remote database in a DELETE, INSERT, SELECT, and UPDATE command.
A database link can be public or private. A public database link is available to all users, while a private database link can only be used by its owner. You need to specify the PUBLIC parameter to create a public database link. If you do not set the parameter, a private database link is created.
To view a list of database links, you can query one of the following views: DBA_DB_LINKS, ALL_DB_LINKS, and USER_DB_LINKS.

Example

Important

When creating a database link, if you receive a message that says oracle_fdw or postgres_fdw does not exist, you can install it by using CREATE EXTENSION.

Create a database link by using oracle_fdw.

CREATE DATABASE LINK ora_dblink
  CONNECT TO admin IDENTIFIED BY 'mypassword'
  USING '//orachannel/acctg';

Create a database link by using postgres_fdw.

CREATE DATABASE LINK pg_dblink
  CONNECT TO admin IDENTIFIED BY "mypassword"
  USING (channel_name 'localhost', dbname 'marketing');

Use the database link.

SELECT * FROM emp@ora_link;

Sample result:

empno | ename  |    job    | mgr  |      hiredate      | sal  | comm | deptno
-------+--------+-----------+------+--------------------+------+------+--------
  7369 | SMITH  | CLERK     | 7902 | 17-DEC-80 00:00:00 |  800 |      |     20
  7499 | ALLEN  | SALESMAN  | 7698 | 20-FEB-81 00:00:00 | 1600 |  300 |     30
  7521 | WARD   | SALESMAN  | 7698 | 22-FEB-81 00:00:00 | 1250 |  500 |     30
  7566 | JONES  | MANAGER   | 7839 | 02-APR-81 00:00:00 | 2975 |      |     20
(4 rows)

Use the database link.

SELECT d.deptno, d.dname, e.empno, e.ename, e.job, e.sal, e.comm FROM
emp@ora_link e, dept@fdwlink d WHERE e.deptno = d.deptno ORDER BY 1, 3;

Sample result:

deptno |   dname    | empno | ename  |    job    | sal  | comm
--------+------------+-------+--------+-----------+------+------
     10 | ACCOUNTING |  7782 | CLARK  | MANAGER   | 2450 |
     10 | ACCOUNTING |  7839 | KING   | PRESIDENT | 5000 |
     10 | ACCOUNTING |  7934 | MILLER | CLERK     | 1300 |
     20 | RESEARCH   |  7369 | SMITH  | CLERK     |  800 |
     20 | RESEARCH   |  7566 | JONES  | MANAGER   | 2975 |
     20 | RESEARCH   |  7788 | SCOTT  | ANALYST   | 3000 |
     20 | RESEARCH   |  7876 | ADAMS  | CLERK     | 1100 |
     20 | RESEARCH   |  7902 | FORD   | ANALYST   | 3000 |
     30 | SALES      |  7499 | ALLEN  | SALESMAN  | 1600 |  300
     30 | SALES      |  7521 | WARD   | SALESMAN  | 1250 |  500
     30 | SALES      |  7654 | MARTIN | SALESMAN  | 1250 | 1400
     30 | SALES      |  7698 | BLAKE  | MANAGER   | 2850 |
     30 | SALES      |  7844 | TURNER | SALESMAN  | 1500 |    0
     30 | SALES      |  7900 | JAMES  | CLERK     |  950 |
(14 rows)