dblink
dblink(Database Link)数据库链接顾名思义就是数据库的链接 [1] ,就像电话线一样,是一个通道,当我们要跨本地数据库,访问另外一个数据库表中的数据时,本地数据库中就必须要创建远程数据库的dblink,通过dblink本地数据库可以像访问本地数据库一样访问远程数据库表中的数据。
- 外文名
- dblink 含 义
- 数据库的链接
- PUBLIC DATABASE LINK系统权限 连接字符串
- connectstring
目录
- 1
- ▪
- 2
- ▪
- ▪
- 3
- ▪
- ▪
- ▪
- ▪
- ▪
- 4
dblink基本信息
dblink格式
1.创建dblink语法:
CREATE [SHARED] [PUBLIC] database link link_name [CONNECT TO [user] [current_user] IDENTIFIED BY password] [AUTHENTICATED BY user IDENTIFIED BY password] [USING 'connect_string']
说明:
1) :创建数据库链接的帐号必须有CREATE DATABASE LINK或CREATE PUBLIC DATABASE LINK的系统权限,用来登录到远程数据库的帐号必须有CREATE SESSION权限。这两种权限都包含在CONNECT角色中(CREATE PUBLIC DATABASE LINK权限在DBA中)。一个公用数据库链接对于数据库中的所有用户都是可用的,而一个私有链接仅对创建它的用户可用。由一个用户给另外一个用户授权私 有数据库链接是不可能的,一个数据库链接要么是公用的,要么是私有的。
2)link : 当source端的参数(parameter)GLOBAL_NAMES=TRUE时,link名必须与远程数据库的全局数据库名global_name)相同;否则,可以任意命名。
3)current_user使用该选项是为了创建global类型的dblink。在分布式体系中存在多个数据库的话。如果想要在每一个数据库中都可以使用同样的名字来访问数据库a,那在每个数据库中都要创建一个到数据库a的db_link,太麻烦了。所以有这个选项的话你只要创建一次。所有的数据库都可以使用这个db_link来访问了。要使用这个特性,必须有oracle nameserver或者ORACLE目录服务器。并且数据库a的参数global_names=true.具体我也没有创建过,没有这个环境。
4)connectstring:连接字符串,tnsnames.ora中定义远程数据库的连接串,也可以在创建dblink的时候直接指定。
5)username、password:远程数据库的用户名,口令。如果不指定,则使用当前的用户名和口令登录到远程数据库,当创建connected user类型的dblink时,需要如果采用数据字典验证,则需要两边数据库的用户名密码一致。
dblinkdblink分类
dblinkdblink的类型及拥有者
类型[2] | Owner | 描述 |
Private | 创建dblink的user拥有该dblink | 在本地数据库的特定的schema下建立的databaselink。只有建立该 databaselink的schema的session能使用这个databaselink来访问远程的数据库。 同时也只有Owner能删除它自己的privatedatabaselink。 |
Public | Owner是PUBLIC. | Public的databaselink是数据库级的,本地数据库中所有的拥有数据库访问权限的用户或pl/sql程序都能使用此databaselink来访问相应的远程数据库。 |
Global | Owner是PUBLIC. | Global的databaselink是网络级的,WhenanOraclenetworkusesadirectoryserver,thedirectoryserverautomaticallycreateandmanagesglobaldatabaselinks(asnetservicenames)foreveryOracleDatabaseinthenetwork .UsersandPL/SQLsubprogramsin anydatabasecanuseagloballinktoaccessobjectsinthecorrespondingremotedatabase. Note:InearlierreleasesofOracleDatabase,aglobaldatabaselinkreferredtoadatabaselinkthatwasregisteredwithanOracleNamesserver.TheuseofanOracleNamesserverhasbeendeprecated.Inthisdocument,globaldatabaselinksrefertotheuseofnetservicenamesfromthedirectoryserver. |
dblinkdblink创建所需权限
Privilege | Database | Required For |
CREATE DATABASE LINK | Local | Creation of a privase database link. |
CREATE PUBLIC DATABASE LINK | Local | Creation of a public database link. |
CREATE SESSION | Remote | Creation of any type of database link. |
dblink基本语法
dblink创建dblink
创建dblink一般有两种方式 [3] ,不过在创建dblink之前用户必须有创建dblink的权限。想知道有关dblink的权限,以sys用户登录到本地数据库:
select * from user_sys_privs t where t.privilege like upper('%link%');
查询结果集 : 1 SYS CREATE DATABASE LINK NO 2 SYS DROP PUBLIC DATABASE LINK NO 3 SYS CREATE PUBLIC DATABASE LINK NO 可以看出在数据库中dblink有三种权限:
1.CREATE DATABASE LINK(所创建的dblink只能是创建者能使用,别的用户使用不了) , 2.CREATE PUBLIC DATABASE LINK(public表示所创建的dblink所有用户都可以使用), 3.DROP PUBLIC DATABASE LINK。 在sys用户下,把CREATE PUBLIC DATABASE LINK,DROP PUBLIC DATABASELINK权限授予给你的用户:
grant CREATE PUBLIC DATABASE LINK,DROP PUBLIC DATABASE LINK to scott;
然后以scott用户登录本地数据库
1、已经配置本地服务 create public database link link_name
connect to username identified by password
using 'connect_string';
注:link_name是连接名字,可以自定义;
username是登陆数据库的用户名;
password是登陆数据库的用户密码;
connect_string是数据库连接字符串。
数据库连接字符串是当前客户端数据库中TNSNAMES.ORA文件里定义的别名名称.可以用NET8 EASY CONFIG或者直接修改TNSNAMES.ORA里定义. 2、直接建立链接 create database link link_name connect to username identified by password using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = X.X.X.X )(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = SSID) ) )'; host=数据库的ip地址,service_name=数据库的ssid。 其实两种方法配置dblink是差不多的,个人感觉还是第二种方法比较好,这样不受本地服务的影响。 注意: 假如创建全局dblink,则必须使用systm或sys用户,在database前加public。
dblinkdblink查询
查看所有的数据库链接,进入系统管理员SQL>操作符下,运行命令:
SQL>select owner,object_name from dba_objects where object_type='DATABASE LINK'; 或者
select * from dba_db_links;
dblinkdblink删除
DROP PUBLIC DATABASE LINK link_name;
dblinkdblink使用
SELECT……FROM 表名@数据库链接名;
查询、删除和插入数据和操作本地的数据库是一样的,只不过表名需要写成“表名@dblink服务器”而已。
例:查询北京数据库中emp表数据 select * from emp@BeiJing;
设此处北京数据库的数据库连接字符串为BeiJing;
dblink同义词配合
例子中from emp@BeiJing可以创建同义词来替代: CREATE SYNONYM 同义词名 FOR 表名;
CREATE SYNONYM 同义词名 FOR 表名@数据库链接名;
如:create synonym bj_scott_emp for emp@BeiJing;
于是就可以用bj_scott_emp来替代带@符号的分布式链接操作emp@BeiJing DB LINK是独立于创建用户(USER_DB_LINKS的USERNAME)起作用的,其他用户无法使用这个连接,无权限也不能删除它。
dblink实例
需要注意的是在oracle参数中,有一个参数叫global_names,如果该参数为TRUE,那么在使用db link时,db link的名字一定要和被访数据库实例名一致,否则会报ORA-2085错。
Machine 1: IP:192.168.100.162 ORACLE_SID:HX Machine 2: IP:192.168.100.4 ORACLE_SID:prepaid 1.在Machine 2的数据库中增加用户 以oracle用户登录Machine 2 $export ORACLE_SID=prepaid $sqlplus / as sysdba SQL>create user test identified by test; User created. SQL>grant connect,resource to test; Grant succeeded. 2.修改Machine 1的tnsnames.ora文件 增加如下内容: prepaid_test= (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.4)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SID = prepaid) ) ) 3.创建db link [4]
以oracle用户登录Machine 1 $export ORACLE_SID=HX $sqlplus / as sysdba SQL>create public database link prepaid_test connect to test identified by test using 'prepaid_test'; Database link created. SQL>show parameter global_names NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ global_names boolean TRUE SQL>select * from dual@prepaid_test; select * from dual@prepaid_test * ERROR at line 1: ORA-02085: database link PREPAID_TEST connects to PREPAID SQL>alter system set global_names=false; System altered. SQL>select * from dual@prepaid_test;
D - X 可以看到,当global_names=false时,db link可以连接远程数据库。 $oerr ora 2085 02085, 00000, "database link %s connects to %s" // *Cause: a database link connected to a database with a different name. // The connection is rejected. // *Action: create a database link with the same name as the database it // connects to, or set global_names=false. //
4.修改Machine 1的tnsnames.ora文件 修改prepaid_test为prepaid: prepaid = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.4)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SID = prepaid) ) ) 5.重新创建db link 以oracle用户登录Machine 1 $export ORACLE_SID=HX $sqlplus / as sysdba SQL>drop public database link prepaid_test; Database link dropped. SQL>create public database link prepaid connect to test identified by test using 'prepaid'; Database link created. SQL>alter system set global_names=true; System altered. SQL>show parameter global_names NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ global_names boolean TRUE SQL>select * from dual@prepaid;
D - X 可以看到,当global_names=true时,如果创建的db link名字与远程数据库的实例名一致,还是可以访问的。