Oracle创建dblink测试

DBLINK是一个数据库中的架构对象，可用于访问另一个数据库上的对象。另一个数据库不一定是 Oracle 数据库系统。但是，要访问非 Oracle 系统，您必须使用 Oracle 异构服务: CREATEDATABASELINK

创建dblink所需权限

Privilege	Database	Required For
CREATE DATABASE LINK	Local	Creation of a private database link.
CREATE PUBLIC DATABASE LINK	Local	Creation of a public database link.
CREATE SESSION	Remote	Creation of any type of database link.

创建dblink测试如下：

一、远端数据库准备条件

• 服务名：orcl
• 端口号：1522
• IP:192.168.245.201
• 连接用户：shuaige
• 连接用户密码：shuaige123
• 连接用户权限：(create session)

---

二、本地数据库准备条件

• 连接用户：shuaige
• 连接用户权限：(create public database link)(create database link)

一、测试网络端口

1、使用nc命令

$ nc -vz <IP> <PORT_NUMBER>

$ nc -vz 192.168.245.201 1522
Ncat: Version 7.50 ( https://nmap.org/ncat )
Ncat: Connected to 192.168.245.201:1522.
Ncat: 0 bytes sent, 0 bytes received in 0.01 seconds.

2、使用tnsping

$ tnsping <IP>:<PORT_NUMBER>

$ tnsping 192.168.245.201:1522

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 01-JUN-2022 11:31:20

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/19.3.0/dbhome_1/network/admin/sqlnet.ora

Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.245.201)(PORT=1522)))
OK (0 msec)

二、本地配置tnsnames.ora文件

vi $ORACLE_HOME/network/admin/tnsnames.ora

添加以下内容：

ORCL_11G =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.245.201)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

三、创建dblink

语法：

CREATE [ SHARED ] [ PUBLIC ] DATABASE LINK dblink
  [ CONNECT TO
    { CURRENT_USER
    | user IDENTIFIED BY password [ dblink_authentication ]
    }
  | dblink_authentication
  ]...
  [ USING connect_string ] ;

（1）创建公共dblink（所有用户可用）

1. 源库创建公共dblink（public_dblink_11g）

SHUAIGE@orcl> CREATE PUBLIC DATABASE LINK public_dblink_11g CONNECT TO shuaige IDENTIFIED BY shuaige123 USING 'ORCL_11G';

Database link created.

或

SHUAIGE@orcl> CREATE PUBLIC DATABASE LINK public_dblink_11g CONNECT TO shuaige IDENTIFIED BY shuaige123 USING '(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.245.201)(PORT = 1522))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = orcl)))';

Database link created.

2. 测试dblink

SHUAIGE@orcl> select * from t1@public_dblink_11g;

	ID NAME
---------- ------------------------------
     54321 lihua

3. 删除dblink

DROP [PUBLIC] DATABASE LINK dblink; 

SHUAIGE@orcl> drop public database link public_dblink_11g;

Database link dropped.

（2）创建私有dblink（仅所属用户可用）

1. 源库创建私有dblink（private_dblink_11g）

SHUAIGE@orcl> CREATE  DATABASE LINK private_dblink_11g CONNECT TO shuaige IDENTIFIED BY shuaige123 USING 'ORCL_11G';

Database link created.

或

SHUAIGE@orcl> CREATE DATABASE LINK private_dblink_11g CONNECT TO shuaige IDENTIFIED BY shuaige123 USING '(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.245.201)(PORT = 1522))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = orcl)))';

Database link created.

2. 测试dblink

所属用户进行dblink连接查询测试

SHUAIGE@orcl> select * from t1@private_dblink_11g;

	ID NAME
---------- ------------------------------
     54321 lihua

非所属用户进行dblink连接查询测试

HR@orcl> select * from t1@private_dblink_11g;
select * from t1@private_dblink_11g
                 *
ERROR at line 1:
ORA-02019: connection description for remote database not found

3. 删除dblink

SHUAIGE@orcl> drop database link private_dblink_11g;

Database link dropped.

其他相关语句：

-- 1、授予用户创建公共dblink权限

grant create public database link to user;

-- 2、授予用户创建私有dblink权限

grant create database link to user;

-- 3、查询具有dblink权限的用户

select * from dba_sys_privs where privilege like upper('%DATABASE LINK%');

-- 4、查询数据库dblink相关数据字典

select * from dba_db_links;

select * from all_db_links;

select * from user_db_links;

-- 5、查询数据库dblink对象

select * from dba_objects where  object_type like '%LINK%';

-- 6、关闭dblink session

alter session close database link my_private_dblink;

-- 7、查询数据库dblink链接最大数,(默认4)
show parameter open

--8、修改数据库dblink链接最大数,(重启实例生效)
alter system set open_links=10 scope=spfile;

dblink连接数据库报错：
OERR: ORA-2085 "database link %s connects to %s"
Reference Note (文档 ID 19367.1)