Purpose
This statement is used to create a dblink for accessing a remote database. You need to specify the name of the dblink, provide the username, tenant name, password, IP address, port number, and access type of the remote database. For a reverse link, you also need to provide the username, tenant name, password, IP address, and port number of the local database. If the access type is not specified, a dblink for accessing a remote OceanBase Database is created by default.
If the remote database is an OceanBase Database, the IP address and port number can be those of an OBServer node in the specified OceanBase cluster or those of the proxy of the OceanBase cluster. If the port number of the dblink is the port number of the proxy and the proxy is deployed by using the ConfigUrl, you must specify the cluster name. If the reverse link feature is required, you also need to provide the IP address and port number of an OBServer node in the local OceanBase cluster, as well as the username, tenant name, and password.
If the remote database is an Oracle database, you must explicitly specify the access type as OCI and specify the oracle_sid. The tenant name is always oracle in this case.
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'];
Parameters
| Parameter | Description |
|---|---|
| dblink_name | The name of the database link 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 login password of the username of the remote database. If the password contains special characters other than digits and letters (~!@#%^&*_-+=|(){}[]:;,.?/), you must enclose the password in double quotation marks to avoid syntax errors. |
| OB | OCI | OB indicates that the remote database is an OceanBase Database. OCI indicates that the remote database is an Oracle database. If neither parameter is specified, the remote database is an OceanBase Database by default. |
| ip | The IP address of the remote database. If the remote database is an OceanBase Database, the IP address can be that of the proxy of the OceanBase cluster or that of an OBServer node in the cluster. If the IP address is that of an OBServer node, the network between the local database and the specified OBServer node must be accessible.
NoteOceanBase Database also supports domain names. For example, |
| port | The port number of the remote database. If the remote database is an OceanBase Database, the port number can be that of the proxy of the OceanBase cluster or that of an OBServer node in the cluster. If the port number is that of an OBServer node, the network between the local database and the specified OBServer node must be accessible. If the remote database is an Oracle database, the IP address is that of the Oracle instance. |
| oracle_sid | The sid of the remote Oracle database. This parameter is required only when the remote database is an Oracle database. |
| cluster_name | The name of the remote OceanBase cluster. This parameter is required only when the IP address and port number are those of the proxy, and the proxy is deployed by using the ConfigUrl. The cluster name must be enclosed in double quotation marks. |
| local_cluster_name | The name of the local OceanBase cluster. This parameter is required only when the IP address and port number are those of the proxy, and the proxy is deployed by using the 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 login password of the username of the local database. If the password contains special characters other than digits and letters (~!@#%^&*_-+=|(){}[]:;,.?/), you must enclose the password in double quotation marks to avoid syntax errors. |
| local_ip | The IP address of an OBServer node in the local database cluster. |
| local_port | The port number of an OBServer node in the local database cluster. |
Examples
Example 1: Create a dblink named ob_dblink_proxy for accessing a remote OceanBase Database. The tenant name of the remote database is oracle by default. The proxy is deployed by using the ConfigUrl. The cluster name must be enclosed in double quotation marks to prevent the cluster name from being capitalized.
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 for accessing a remote OceanBase Database with a reverse link. The tenant name 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 for accessing a remote Oracle database. The tenant name 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