Oracle DBLINK

--DBLINK

--创建dblink语法
CREATE [PUBLIC] DATABASE LINK linkname 
CONNECT TO username IDENTIFIED BY password
USING 'connectstring';

--说明
--1) 权限:创建数据库链接的帐号必须有CREATE DATABASE LINK或CREATE PUBLIC DATABASE LINK的系统权限，用来登录到远程数据库的帐号必须有CREATE SESSION权限。这两种权限都包含在CONNECT角色中（CREATE PUBLIC DATABASE LINK权限在DBA中）。一个公用数据库链接对于数据库中的所有用户都是可用的，而一个私有链接仅对创建它的用户可用。由一个用户给另外一个用户授权私有数据库链接是不可能的，一个数据库链接要么是公用的，要么是私有的
--2)link:当GLOBAL_NAME=TRUE时，link名必须与远程数据库的全局数据库名global_name）相同；否则，可以任意命名
  --修改GLOBAL_NAME的方法：
  --A.在远程数据库的init.ora文件中将global_names设为false
  --或者
  --B.用sys用户执行如下语句:ALTER SYSTEM SET GLOBAL_NAME=TRUE/FALSE;
  --修改后重新启动数据库设置才能生效
  --数据库全局名称可以用以下命令查出:SELECT * FROM GLOBAL_NAME;
--3)connectstring：连接字符串，tnsnames.ora中定义远程数据库的连接串
--4)username、password：远程数据库的用户名，口令。如果不指定，则使用当前的用户名和口令登录到远程数据库

--删除数据库链接的语句
DROP [PUBLIC] DATABASE LINK linkname;

--查询dblinke
set line 333
col owner for a15
col db_link for a15
col username for a15
col host for a30
select * from dba_db_links;
select owner,object_name from dba_objects where object_type='DATABASE LINK';

--当前正在使用的database link
select * from v$dblink;

--从dba_db_links获取其db_name及ip地址
set linesize 300
col username for a30
col db_link for a30
col service_name for a30
select first_level.owner,
       first_level.db_link,
       substr(substr(first_level.host, first_level.sern_first_pos, 100),
              1,
              instr(substr(first_level.host, first_level.sern_first_pos, 100),
                    ')') - 1) as service_name
  from (select owner,
               db_link,
               host,
               instr(host, 'SERVICE_NAME') as sern_first_pos
          from dba_db_links
         where instr(host, 'SERVICE_NAME') > 0) first_level;

--获取dblink-ip及host信息
set linesize 300
col owner for a15
col username for a15
col db_link for a25
col host for a25 
col first_ip for a25
col second_ip for a25
col service_name for a25
select first_level.owner,
       first_level.db_link,
       substr(substr(first_level.host, first_level.sern_first_pos, 100),
              1,
              instr(substr(first_level.host, first_level.sern_first_pos, 100),
                    ')') - 1) as service_name,
       substr(first_level.first_ip_addr,
              1,
              instr(first_level.first_ip_addr, ')') - 1) as first_ip,
       substr(first_level.second_ip_addr,
              1,
              instr(first_level.second_ip_addr, ')') - 1) as second_ip
  from (select owner,
               db_link,
               host,
               instr(host, 'SERVICE_NAME') as sern_first_pos,
               substr(host, instr(host, 'HOST'), 30) as first_ip_addr,
               substr(host, instr(host, 'HOST', 1, 2), 30) as second_ip_addr
          from dba_db_links
         where instr(host, 'SERVICE_NAME') > 0) first_level;
     

--数据库版本
select * from v$version where rownum=1;

--操作系统版本
host more /etc/*release

--dblink的引用
[user.]table|view@dblink
--例如
SELECT * FROM worker@hk_link;
SELECT * FROM hk.worker@hk_link;

--注
--1.dblink分为公有和私有两类。
--公有dblink使用public修饰关键字。在create和drop的时候都需要使用public关键字
--公有dblink对所有人开放，在该dblink之上创建的同义词也会随之对所有人开放。（测试并确认，不过测试是在一个实例多个用户之间进行）
--私有dblink只有创建者可以访问，其上的同义词不能被其他用户访问。需为用户创建视图，并将视图授权给所需用户后，用户才可访问该视图
--另外，不能将带有dblink的同义词直接授权给用户。否则报错，其等价于：
--grant select on table1@user1 to user2;
--ERROR at line 1:
--ORA-02021: DDL operations are not allowed on a remote database
--2.创建dblink时，可以使用连接字符串（与tnsname.ora中的），效率较高

--创建可以采用两种方式
--1、已经配置本地服务
create DATABASE LINK 数据库链接名CONNECT TO 用户名 IDENTIFIED BY 密码 USING '本地配置的数据的实例名'; 
create public database link hking connect to scott identified by tiger using 'hking';
--2、未配置本地服务
create database link hking 
connect to scott identified by tiger 
using 
'(DESCRIPTION = 
(ADDRESS_LIST = 
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.142.202.12)(PORT = 1521)) 
) 
(CONNECT_DATA = 
(SERVICE_NAME = hking) 
) 
)';