Purpose
You can use this statement to create a dblink for accessing a specified remote database. In this statement, you must specify the dblink name, username, tenant name, password, IP address, port number, and access type of the remote database. When you create a reverse link, you must specify the username, tenant name, password, IP address, and port number of the local database. If the access type is not specified, this statement creates a dblink for accessing a remote OceanBase database.
If the remote database is an OceanBase database, the IP address and port number can be the IP address and port number of an OBServer node in a specified remote OceanBase cluster or the IP address and port number of an ODP in the OceanBase cluster. If the port number of an ODP in a cluster is used and ODP is deployed by using a ConfigURL, you must specify the cluster name. If you want to use the reverse link feature, you must also specify 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 set the access type to OCI, specify oracle_sid, and set the tenant name to 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'];
Parameters
| Parameter | Description |
|---|---|
| dblink_name | The name of the database link to access. |
| 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 corresponding to the username of the remote database. If the password contains special characters other than digits and letters, it must be enclosed in double quotation marks to avoid syntax errors. Special characters are ~ ! @ # % ^ & * _ - + = | ( ) { } []: ; , . ? / |
| OB | OCI | OB indicates that the remote database is an OceanBase database. OCI indicates that the remote database is an Oracle database. If neither of the parameters is specified, the remote database is an OceanBase database. |
| ip | The IP address of the remote database. If the remote database type is OceanBase Database, you can set the IP address to the IP address of an ODP in the cluster or to the IP address of an OBServer node in the cluster. If you set the IP address of the remote database to the IP address of an OBServer node, the local database and the specified OBServer node must be able to communicate with each other over network. |
| port | The port number of the remote database. If the remote database type is OceanBase Database, you can set the port number to the port number of an ODP in the cluster or to the port number of an OBServer node in the cluster. If you set the port number of the remote database to the port number of an OBServer node, the local database and the specified OBServer node must be able to communicate with each other over network. If the remote database is an Oracle database, the IP address of the Oracle instance is used. |
| 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 of an ODP are used and the ODP is deployed by using a 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 of an ODP are used and the ODP is deployed by using a 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 logon password of the local database. If the password contains special characters other than digits and letters, it must be enclosed in double quotation marks to avoid syntax errors. Special characters are ~ ! @ # % ^ & * _ - + = | ( ) { } link[1]link: ; , . ? /[] |
| 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 to connect to a remote OceanBase database. The default tenant name of the remote database is oracle. The ODP is deployed by using a ConfigURL. The cluster name must be enclosed in double quotation marks to prevent the letters 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 with a reverse link to connect to a remote OceanBase database. 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 to connect to 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