Manage DBLinks

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.

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 of your local OceanBase database to an Oracle tenant of the remote OceanBase database.

SQL syntax for creating a DBLink:

obclient> CREATE DATABASE LINK dblink_name CONNECT TO user@tenant IDENTIFIED BY {$password} [OB] HOST 'ip:port' [CLUSTER cluster_name];

Statement usage notes:

• To execute this statement, you must have the CREATE DATABASE LINK privilege. For information about how to grant user privileges, see Modify user privileges.

• 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.

• OB: specifies that the remote database is an OceanBase database.

  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 OceanBase Database Proxy (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 an OBServer node, the local database and the specified OBServer node must be interconnected.

  Note

  The current version also supports domain names for this parameter. You can enter a domain name to connect to the remote OceanBase database based on the actual environment.

• port: the port number of the remote OceanBase database. It can be the ODP port number of the cluster or the port number of an OBServer node in the cluster.

  If the parameter is set to the port number of an OBServer node, the local database and the specified OBServer node must be interconnected.

• 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.

  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.

Examples:

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

  obclient> CREATE DATABASE LINK ob_dblink CONNECT TO ob_user@oracle IDENTIFIED BY *** OB HOST '10.10.10.1:12345';
  

• 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 '10.10.10.2:30084' CLUSTER "ob323";
  

  Notice

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

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.

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

Statement usage notes:

• To execute this statement, you must have the CREATE DATABASE LINK privilege. For information about how to grant user privileges, see Modify user privileges.

• 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.

• 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.

  Note

  The current version also supports domain names for this parameter. You can enter a domain name to connect to the remote OceanBase database based on the actual environment.

• 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 '10.10.10.2:1521/ORCL';

View a DBLink

You can use sys.all_virtual_dblink_agent to view the created DBLinks.

Syntax:

obclient> SELECT * FROM sys.all_virtual_dblink_agent;

The following table describes the fields in the query result.

Use a DBLink to access data in a remote database

You can use SELECT... FROM table name @ DBLink name to query data in a remote database. Currently, a DBLink does not support data modification, insertion, or deletion.

SQL syntax:

SELECT select_expr_list
      FROM table_name@dblink_name
      WHERE where_conditions;

select_expr_list:
    table_name.*
    | table_alias_name.*
    | expr [[AS] column_alias_name]

Statement usage notes:

• select_expr_list: the expressions or column names to be queried. Separate the column names with commas (,). An asterisk (*) indicates all columns.

  • table_name.*: all columns in the specified table or view.

  • table_alias_name.: the alias of the table or view.

  • expr [[AS] column_alias_name]: the alias of the column or expression that you want to query. AS is optional.

• table_name @ dblink_name: the tables to be queried in the remote database. Separate the tables with commas (,).

• where_conditions: the filter condition, which is optional. Only the data that meets the condition is returned.

Example: Use the DBLink named my_link to query data in the tbl1 table in a remote database.

obclient> SELECT * FROM tbl1@my_link;

For more DBLink query statements, see SIMPLE SELECT.

Drop a DBLink

You can drop a DBLink that is no longer required. Syntax:

obclient> DROP DATABASE LINK dblink_name;

Statement usage notes:

• To execute this statement, you must have the DROP DATABASE LINK privilege. For information about how to grant user privileges, see Modify user privileges.

• dblink_name specifies the name of the DBLink to be dropped.

Example:

obclient> DROP DATABASE LINK ob_dblink;
Query OK, 1 row affected

obclient> DROP DATABASE LINK orcl_dblink;
Query OK, 1 row affected