DBLINK的使用详解
测试环境:
源端OS WIN10 ORACLE:12.2.0.1
远端OS RHEL7.6 ORACLE:11.2.0.4
一、前期准备
1.源端创建到远端数据库的连接别名(过程略)
有一点要注意,service_name,如果有pdb,进入之后可用dba_services视图,语句查询:
select name,pdb,GLOBAL_SERVICE from dba_services;
测试如下:
C:\Users\xmc>tnsping dbl
TNS Ping Utility for 64-bit Windows: Version 12.2.0.1.0 - Production on 11-12月-2020 14:30:36
Copyright (c) 1997, 2016, Oracle. All rights reserved.
已使用的参数文件:
D:\app\xmc\virtual\product\12.2.0\dbhome_1\network\admin\sqlnet.ora
已使用 TNSNAMES 适配器来解析别名
尝试连接 (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.12.66)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = xmc)))
OK (0 毫秒)
2.查看源端用户的create dblink权限
当前用户下
SELECT * FROM USER_SYS_PRIVS WHERE PRIVILEGE LIKE '%DATABASE LINK%' AND USERNAME='ZHANGS';
这里注意:如果zhangs赋予了dba角色权限,这里返回不了结果,但能创建,原因大家都知晓不在赘言。
3.
如果没有相关创建权限,赋予权限
私有或者说当前用户dblink
grant database link to zhangs;
公有dblink
grant public database link to zhangs;
二、实施
1.
创建public dblink
语法1:create public database link linkname using ‘dbl’;
这里默认源库当前用户和密码去连接远端数据库(也有相同用户和密码)
语法2:create public database link linkname connect to username identified by password using ‘dbl’;
这里username注意是远端库的,而不是源端的。
语法3:create public database link linkname connect to username identified by password using ‘
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.12.66)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = xmc)
)
)’;
这里是不配置别名的情况下使用的。
附:创建私有dblink举例,语法同上
create database link linkname connect to username identified by password using ‘dbl’;
2.
源端测试
select sysdate from dual@linkname;
或
select * from xmc.t2@xmc_recover
如下:
源端
SYSDATE
--------------
11-12月-20
远端:
SYSDATE
---------
01-DEC-20
远端创建表tlink
源端:select * from tab@linkname
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TLINK TABLE
说明是成功的。
3.查询所有dblink
相关视图:all_db_links、dba_db_links、dba_objects
举例如下:
select owner,db_link ,username from all_db_links;
select owner,db_link,USERNAME from dba_db_links;
select owner,object_name from dba_objects where object_type='DATABASE LINK';
4.删除指定dblink
drop pulic database link linkname;
5.global_names说明
如果global_names在源端(不管远端是true还是false)是true,则linkname必须为远端的global_names,可通过如下语句查看
select * from global_name;
查看global_names的值
sho parameter name;
如果权限不足,可以通过有权限的用户查看
修改global_names的值
alter system set global_names=false;
alter system set global_names=true;