Description
This statement creates a dblink to access a specified remote database. To execute this statement, you must specify the name of the dblink, as well as the username, tenant name, password, IP address, port number, and access type of the remote database. For a reverse LINK, you must specify the username, tenant name, password, IP address, and port number of the local database. If you do not specify the access type, the system will create a dblink to access OceanBase Database by default.
If the remote database is an OceanBase database, you can specify the IP address and port number of a specific OBServer node in the remote OceanBase cluster or the IP address and port number of an OceanBase proxy for the cluster. If the port number you specify is the port number of an OceanBase proxy and you deployed the proxy by using ConfigUrl, you must specify the name of the cluster. If you want to create a reverse LINK, you must also specify the IP address and port number, as well as the username, tenant name, and password, of an OBServer node in the local OceanBase cluster.
If the remote database is an Oracle database, you must specify the access type OCI, the oracle_sid, and the tenant name oracle.
Syntax
CREATE DATABASE LINK dblink_name CONNECT TO user_name@tenant_name
IDENTIFIED BY password_str [OB|OCI] HOST 'ip:port[/oracle_sid]' [CLUSTER cluster_name] [MY_NAME local_user_name@local_tenant_name
IDENTIFIED BY local_password_str HOST 'local_ip:local_port'];
Notes
Parameter |
Description |
|---|---|
| dblink_name | The name of the dblink to be accessed. |
| user_name | The username of the remote database. |
| tenant_name | The tenant name of the remote database. If the remote database is an Oracle database, the tenant name is always oracle. |
| password | The password for logging in to the username of the remote database. If the password contains special characters other than digits and letters (~!@#%^&*_-+=|(){}[]:;,.?/ ), you must enclose it in double quotation marks to avoid syntax errors. |
| OB | OCI | Specifies the type of the remote database. OB indicates that the remote database is an OceanBase database. OCI indicates that the remote database is an Oracle database. If you do not specify either parameter, OceanBase Database is assumed to be the remote database. |
| ip | The IP address of the remote database. If the remote database is an OceanBase database, you can specify the IP address of the OceanBase proxy or the IP address of an OBServer node in the cluster. If you specify the IP address of an OBServer node, make sure that the network between the local database and the specified OBServer node is accessible. |
| port | The port number of the remote database. If the remote database is an OceanBase database, you can specify the port number of the OceanBase proxy or the port number of an OBServer node in the cluster. If you specify the port number of an OBServer node, make sure that the network between the local database and the specified OBServer node is accessible. If the remote database is an Oracle database, the IP address is the IP address of the Oracle instance. |
| oracle_sid | The sid of the remote Oracle database. You must specify this parameter only if the type of the remote database is Oracle. |
| cluster_name | The name of the remote OceanBase cluster. You must specify this parameter only if the port number you specify is the port number of an OceanBase proxy deployed by using ConfigUrl. The cluster name must be enclosed in double quotation marks. |
| local_cluster_name | The name of the local OceanBase cluster. You must specify this parameter only if the port number you specify is the port number of an OceanBase proxy deployed by using ConfigUrl. The cluster name must be enclosed in double quotation marks. |
| local_user | The username of the local database. |
| local_tenant | The tenant name of the local database. |
| local_password | The password for logging in to the username of the local database. If the password contains special characters other than digits and letters (~!@#%^&*_-+=|(){}[]:;,.?/ ), you must enclose it in double quotation marks to avoid syntax errors. |
| local_ip | The IP address of an OBServer node in the local OceanBase cluster. |
| local_port | The port number of an OBServer node in the local OceanBase cluster. |
Examples
Example 1: Create a dblink named ob_dblink_proxy to connect to a remote OceanBase database. The tenant of the remote database uses the default oracle user. The proxy is deployed by using ConfigUrl. The cluster name must be enclosed in double quotation marks to prevent the uppercase conversion of its letters.
obclient> CREATE DATABASE LINK ob_dblink_proxy CONNECT TO ob_testuser@oracle IDENTIFIED BY **1** OB HOST '10.XXX.XXX.XXX:30084' CLUSTER "ob***";
Query OK, 1 row affected
Example 2: Create a dblink named ob_dblink_reverse_link with a reverse link to connect to a remote OceanBase database. The tenant of the remote database is oracle.
obclient> CREATE DATABASE LINK ob_dblink_reverse_link CONNECT TO ob_testuser2@oracle IDENTIFIED BY **1** OB HOST '10.XXX.XXX.XXX:35305' MY_NAME local_ob_testuser@oracle identified by **2** host '10.XXX.XXX.XXX:35307';
Query OK, 1 row affected
Example 3: Create a dblink named orcl_dblink to connect to a remote Oracle database. The tenant of the remote database is oracle.
obclient> CREATE DATABASE LINK orcl_dblink CONNECT TO orcl_testuser@oracle IDENTIFIED BY **1** OCI HOST '10.XXX.XXX.XXX:1521/ORCL';
Query OK, 1 row affected