Create a DBLink|V4.1.0| docs|Distributed Database

Create a DBLink

Last Updated：2023-08-01 06:02:28 Updated

OceanBase Database provides DBLinks to support cross-data source access. You can use a DBLink to access a remote database from your local database. DBLinks are supported only in Oracle mode.

You can create a DBLink between two Oracle tenants of OceanBase Database or between an Oracle tenant of OceanBase Database and an Oracle database.

Limitations

DBLinks are not supported by OceanBase Database in MySQL mode.

Prerequisites

You must have the CREATE DATABASE LINK privilege. For information about how to grant user privileges, see Modify user privileges.

Create a DBLink from an OceanBase database to another OceanBase database

To access a remote OceanBase database, you can create a DBLink from an Oracle tenant in your local OceanBase database to an Oracle tenant of the remote OceanBase database.

To create a DBLink, you must specify the DBLink name and provide information, such as the username, tenant name, password, IP address, port number, and access type, of the remote database. The data write feature of DBLink supports reverse links. The remote database can access objects, such as tables, views, and synonyms, in the local database through a reverse link. To use the reverse link feature, you must provide information, such as the username, tenant name, password, IP address, and port number, of the local database when you create a DBLink.

SQL syntax for creating a DBLink from an OceanBase database to another OceanBase database:

obclient> CREATE DATABASE LINK dblink_name CONNECT TO user@tenant IDENTIFIED BY password [OB] HOST 'ip:port' [CLUSTER cluster_name]
       [MY_NAME local_user@local_tenant IDENTIFIED BY local_password HOST 'local_ip:local_port' [CLUSTER local_cluster_name]];

Parameters:

dblink_name: the name of the DBLink. The value can contain a maximum of 128 characters.
user: the username for logging on to the remote OceanBase database.
tenant: the tenant name of the remote OceanBase database. Only Oracle tenants are supported.
password: the password for logging on to the remote OceanBase database. If the password contains a special character, such as @ # !, you must enclose the password in a pair of double quotation marks (" ") to avoid syntax errors.
OB: specifies that the type of the remote database is OceanBase. This parameter is optional.

If this parameter is not specified, the remote database is an OceanBase database.
ip: the IP address of the remote OceanBase database. It can be the ODP IP address of the cluster or the IP address of an OBServer node in the cluster.

If the parameter is set to the IP address of the OBServer node, make sure that the local database can communicate with the specified OBServer node.
port: the port number of the remote OceanBase database. It can be the ODP port number of the cluster, which is 2883 by default, or the port number of an OBServer node in the cluster, which is 2881 by default.

If the parameter is set to the SQL port number of the OBServer node, make sure that the local database can communicate with the specified OBServer node.
cluster_name: the name of the cluster on the remote OceanBase database. You need to specify the cluster name only when the specified IP address and port number are those of the ODP of the cluster and the ODP is deployed by using ConfigUrl. The cluster name must be enclosed in a pair of double quotation marks (" ").
Note

ODP can be deployed in one of the following two modes:
- ConfigUrl: When ODP is started, the obproxy_config_server_url parameter is specified in the command to query the RootServer information of the OceanBase cluster.
- RsList: When ODP is started, the -r parameter is specified in the command to query the RootServer information of the OceanBase cluster.
[MY_NAME local_user@local_tenant IDENTIFIED BY local_password HOST 'local_ip:local_port' [CLUSTER local_cluster_name]]: the username, tenant name, password, IP address, port number, and cluster of the local database, which are required only if you want to use the reverse link feature of DBLink.
- local_user: the username of the local database.
- local_tenant: the name of the tenant to which the local database belongs.
- local_password: the password used to log on to the local database. If the password contains a special character, such as @ # !, you must enclose the password in a pair of double quotation marks (" ") to avoid syntax errors.
- local_ip: the IP address of an OBServer node in the cluster of the local database.
- local_port: the SQL port number of an OBServer node in the cluster of the local database. By default, the SQL port number of the OBServer node is 2881.
- local_cluster_name: the name of the cluster of the local OceanBase database. You need to specify the cluster name only when the specified IP address and port number are those of the ODP of the cluster and the ODP is deployed by using ConfigUrl. The cluster name must be enclosed in a pair of double quotation marks (" ").

Example:

Create a DBLink to connect to a remote OceanBase database by using the IP address and port number of the specified OBServer node.

obclient> CREATE DATABASE LINK ob_dblink CONNECT TO ob_user@oracle IDENTIFIED BY ****** OB HOST 'xx.xx.xx.xx:2881';
Query OK, 1 row affected

Create a DBLink to connect to a remote OceanBase database by using the IP address and port number of the ODP of a cluster. The ODP is deployed by using ConfigUrl.
```
obclient> CREATE DATABASE LINK ob_dblink_proxy CONNECT TO ob_user@oracle IDENTIFIED BY ****** OB HOST 'xx.xx.xx.xx:2883' CLUSTER "ob410";
Query OK, 1 row affected
```
Notice

Enclose the cluster name in a pair of double quotation marks (" ") to prevent the cluster name from being capitalized.

Create a DBLink named ob_dblink_reverse_link that connects to a remote OceanBase database and provides the reverse link feature.


obclient> CREATE DATABASE LINK ob_dblink_reverse_link CONNECT TO ob_user2@oracle IDENTIFIED BY ****** OB HOST  'xx.xx.xx.xx:2881' MY_NAME local_ob_user@oracle IDENTIFIED BY ****** HOST 'xx.xx.xx.xx:2881';
Query OK, 1 row affected

Create a DBLink from an OceanBase database to an Oracle database

To access a remote Oracle database, you can create a DBLink from an Oracle tenant in your local OceanBase database to the remote Oracle database. DBLink only supports read-only operations.

To create a DBLink, you must specify the DBLink name and provide information, such as the username, tenant name, password, IP address, port number, and access type, of the remote database. The data write feature of DBLink supports reverse links. The remote database can access objects, such as tables, views, and synonyms, in the local database through a reverse link. The DBLink from an OceanBase database to an Oracle database does not support reverse links.

SQL syntax for creating a DBLink from an OceanBase database to an Oracle database:

obclient> CREATE DATABASE LINK dblink_name CONNECT TO user@oracle IDENTIFIED BY password OCI HOST 'ip:port/oracle_sid';

Parameters:

dblink_name: the name of the DBLink. The value can contain a maximum of 128 characters.
user: the username for logging on to the remote Oracle database.
oracle: The value is always oracle if the remote database is an Oracle database.
password: the password for logging on to the remote Oracle database. If the password contains a special character, such as @ # !, you must enclose the password in a pair of double quotation marks (" ") to avoid syntax errors.
OCI: specifies that the type of the remote database is Oracle. If this parameter is not specified, the remote database type is OceanBase by default.
ip: the IP address of the remote Oracle Database instance.
port: the port number of the remote Oracle Database instance.
oracle_sid: the SID of the remote Oracle database.

Example of creating a DBLink to connect to a remote Oracle database:

obclient> CREATE DATABASE LINK orcl_dblink CONNECT TO orcl_user@oracle IDENTIFIED BY ****** OCI HOST 'xx.xx.xx.xx:1521/ORCL';
Query OK, 1 row affected

More information

For more information about operations on DBLinks, see the following topics: