CREATE DATABASE LINK

Description

Creates a database link object. For details about database links, see DATABASE LINK.

Precautions

• The database link feature can be used only in ORA-compatible versions.

• Do not use database links to connect to the initial user.
• Initial users are not allowed to create, modify, or delete database links.
• If the upgrade is not committed, the database link cannot be created.
• If the CURRENT_USER is used or the CONNECT TO connection string is omitted, the initial username and empty password of the current database are used for connection, resulting in connection failure.

Syntax

CREATE [ PUBLIC ] DATABASE LINK dblink
  [ CONNECT TO { CURRENT_USER | 'user' IDENTIFIED BY 'password' } ] [ USING ( option 'value' [...]) ];

Parameters

• PUBLIC

  Creates a public database link visible to all users. If this clause is omitted, the database link is private and available only to the current user.

• dblink

  Indicates the name of the database link to be created.

• user

  Indicates the name of the user for connecting to the remote end of the created database link.

• password

  Indicates the password of the user for connecting to the remote end of the created database link.

• CURRENT_USER

  Uses the initial username and empty password of the current database for connection.

• USING ( option 'value' [, ... ] )

  Specifies parameters such as the IP address, port number, and remote database name of the database to be connected. The supported options are as follows:

  • host: specifies the IP addresses to be connected. IPv6 addresses are not supported. Multiple IP addresses can be specified using character strings separated by commas (,). Currently, encrypted databases, SSL settings, and certificate authentication are not supported. If no IP address is specified, this parameter is left empty by default.
  • port: specifies the port number for connection. If this parameter is not specified, the default value 5432 is used.
  • dbname: specifies the name of the database to be connected. If this parameter is not specified, the username used for connecting to the remote end is used by default.
  • fetch_size: specifies the amount of data obtained from the remote end each time. The value of fetch_size ranges from 0 to 2147483647. The default value is 100.
  

  • You can write only part of the preceding options in the brackets after USING.
  • If the keyword USING is not written, the content in the brackets is not written as well.
  • When a database link is created, the system does not check whether the connection is successful. If related keywords are missing, an error may be reported.
  • Do not use 127.0.0.1 or local host for the host parameter. Otherwise, the connection fails.

Examples

-- Create an ORA-compatible database.
gaussdb=# CREATE DATABASE ora_test_db DBCOMPATIBILITY 'ORA';

-- Switch to another database.
gaussdb=# \c ora_test_db

-- Create a user with the system administrator permission.
ora_test_db=# CREATE USER user1 WITH SYSADMIN PASSWORD '********';
ora_test_db=# SET ROLE user1 PASSWORD '********';

-- Create a private database link.
ora_test_db=# CREATE DATABASE LINK private_dblink CONNECT TO 'user1' IDENTIFIED BY '********' USING (host '192.168.11.11',port '54399',dbname 'db01');

-- Delete the private database link.
ora_test_db=# DROP DATABASE LINK private_dblink;

-- Create a public database link.
ora_test_db=# CREATE PUBLIC DATABASE LINK public_dblink CONNECT TO 'user1' IDENTIFIED BY '********' USING (host '192.168.11.11',port '54399',dbname 'db01');

-- Delete the public database link.
ora_test_db=# DROP PUBLIC DATABASE LINK public_dblink;

-- Delete the created user.
ora_test_db=# RESET ROLE;
ora_test_db=# DROP USER user1;

-- Switch back to the initial database and delete the test database. Replace postgres with the actual database name.
ora_test_db=# \c postgres
gaussdb=# DROP DATABASE ora_test_db;

Helpful Links

ALTER DATABASE LINK and DROP DATABASE LINK