Description
This statement is used to create a dblink for accessing a specified remote database. It requires specifying the dblink name and providing the remote database's username, tenant name, database name, password, IP address, port number, and cluster name.
In OceanBase AI Database, currently only user tenants of a local OceanBase AI Database cluster can read data from user tenants of a remote OceanBase AI Database cluster. You can also create a dblink between different user tenants within the same cluster. Reading data from native MySQL databases or reading data from user tenants of an OceanBase AI Database cluster by native MySQL databases is not supported.
The dblink of OceanBase AI Database supports reading the following data types.
Numeric data types:
TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT,FLOAT,DOUBLE, andDECIMAL.Time types:
DATE,TIME,YEAR,DATETIME, andTIMESTAMP.Character types:
CHAR,VARCHAR,TINYBLOB,TINYTEXT,BLOB,TEXT,MEDIUMTEXT,LONGBLOB, andLONGTEXT.Other types:
ENUMandSET.
Syntax
CREATE DATABASE LINK [IF NOT EXISTS] dblink_name CONNECT TO user_name@tenant_name DATABASE database_name IDENTIFIED BY password HOST 'ip:port' [CLUSTER cluster_name]
Description
field |
Description |
|---|---|
| dblink_name | Specifies the name of the database link to access. |
| IF NOT EXISTS | If specified IF NOT EXISTS, even if the dblink to be created already exists, no error will be reported. If it is not specified and the dblink to be created already exists, the system will report an error. |
| user_name | Specifies the username of the remote database user. |
| tenant_name | Specifies the tenant name of the remote database. |
| password | Specifies the login password for the username of the remote database. If the password contains special characters other than digits and letters (~!@#%^&*_-+=|(){}[]:;,.?/), you must enclose the password in single quotes or double quotes to avoid syntax errors. |
| database_name | The database name of the remote user tenant. When reading data via dblink, this database is accessed by default. To access another database, you can specify the target database for a table in the SQL statement. |
| ip | Specify the IP address of the remote database. You can specify the IP address of the cluster's proxy or the IP address of an OBServer node in the cluster. If you specify the IP address of an OBServer node, the network between the local database and the specified OBServer node must be accessible.
NoteIn addition to IP addresses, OceanBase AI Database also supports domain names. For example, |
| port | Specifies the port number of the remote database. You can specify the port number of the cluster's proxy or the port number of an OBServer node in the cluster. If you specify the port number of an OBServer node, the network between the local database and the specified OBServer node must be accessible. |
| cluster_name | Specifies the name of the remote OceanBase AI Database cluster. The cluster name is required only if the IP address and port number belong to the proxy and the proxy is deployed using the ConfigUrl method. The cluster name is case-sensitive and must be enclosed in double quotation marks. |
Examples
Create a dblink named
ob_dblinkto connect to an OBServer node in a remote OceanBase AI Database cluster. The tenant of the remote database ismysql(you can specify any user tenant), the user isuser1, and the database istest. Specify the password, IP address, and port number according to your actual environment.obclient> CREATE DATABASE LINK IF NOT EXISTS ob_dblink CONNECT TO user1@mysql DATABASE test IDENTIFIED BY '******' HOST 'xx.xx.xx.xx:xx';Create a dblink named
ob_dblink_proxyto connect to the remote OceanBase AI Database cluster. The tenant of the remote database is the defaultmysql(you can specify any user tenant), the user isuser1, and the database istest. Specify the password, IP address, and port number according to your actual environment.obclient> CREATE DATABASE LINK IF NOT EXISTS ob_dblink_proxy CONNECT TO user1@mysql DATABASE test IDENTIFIED BY '******' HOST 'xx.xx.xx.xx:xx' CLUSTER "obcluster";
After creating a Dblink, you can query and delete it. For more information, see SELECT.
You can query the oceanbase.DBA_DB_LINKS view for information about the created DBLINKs. For more information, see DBA_DB_LINKS;