oracle应用dblink交换数据
两个oracle数据库间需要数据交换,需要两个服务器在同一局域网中或其中之一有公网IP,其实就确保网络连接畅通。需要通过dblink来实现,在其中一个创建另一个数据库的dblink,然后就可以直接访问另一个,对用户来说是透明的。
创建dblink
创建dblink一般有两种方式,不过在创建dblink之前用户必须有创建dblink的权限。想知道有关dblink的权限,以sys用户登录到本地数据库:
select * fromuser_sys_privs t
wheret.privilege like upper('%link%');
1 SYS CREATEDATABASE LINK NO
2 SYS DROPPUBLIC DATABASE LINK NO
3 SYS CREATEPUBLIC DATABASE LINK NO
可以看出在数据库中dblink有三种权限CREATE DATABASE LINK(所创建的dblink只能是创建者能使用,别的用户使用不了),CREATE PUBLIC DATABASE LINK(public表示所创建的dblink所有用户都可以使用),DROP PUBLIC DATABASE LINK。
在sys用户下,把CREATE PUBLIC DATABASE LINK,DROP PUBLICDATABASE LINK权限授予给你的用户
grant CREATEPUBLIC DATABASE LINK,DROP PUBLIC DATABASE LINK toscott;
然后以scott用户登录本地数据库
1.创建dblink的第一种方式,是在本地数据库tnsnames.ora文件中配置了要远程访问的数据库。
create publicdatabase link
to_web connectto scott identified by tiger using 'web';
其中to_web是你创建的dblink名字,web是远程数据库的实例名,scott/tiger是登录到远程数据库的用户/密码。然后在本地数据库中通过dblink访问远程数据库'web'中scott.tb_test表,sql语句如下所示
select * fromscott.tb_test@to_web;
2.创建dblink的第二种方式,是在本地数据库tnsnames.ora文件中没有配置要访问的远程数据库,
createdatabase link to_web
connect toscott identified by tiger
using'(DESCRIPTION =
(ADDRESS_LIST=
(ADDRESS =(PROTOCOL = TCP)(HOST = 192.168.1.5)(PORT = 1521))
)
(CONNECT_DATA=
(SERVER =DEDICATED)
(SERVICE_NAME= web)
)
)';
第二种是把第一种配置在tnsnames.ora文件中的信息,直接放在创建dblink语句后面。第一种情况tnsnames.ora文件中信息如下:
web=
(DESCRIPTION =
(ADDRESS_LIST=
(ADDRESS =(PROTOCOL = TCP)(HOST = 192.168.1.5)(PORT = 1521))
)
(CONNECT_DATA=
(SERVER =DEDICATED)
(SERVICE_NAME= web)
)
)
使用dblink
1.查看dblink
select owner,object_name from dba_objects where object_type='DATABASE LINK';
或者
select * from dba_db_links;
2.创建dblink
前提:
创建dblink的用户有对应的数据库权限
create public database link 或者create database link
可以使用下面来授权.
grant create public database link,create database link to myAccount;
createpublic database link dblinkname connect to username identified by password
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = database_ip)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME =servicename)
)
)';
如果在create之后不加public,则创建的dblink就不是公共的,就只有创建者可以使用了.
3.使用db link
例如,在本机数据库上创建了一个scott_rmthost的public dblink(使用远程主机的scott用户连接),则用sqlplus连接到本机数据库,执行select * from scott.emp@scott_rmthot即可以将远程数据库上的scott用户下的emp表中的数据获取到.
也可以在本地建一个同义词来指向scott.emp@scott_rmthost,这样取值就方便多了.
CreateSYNONYM同义词名FOR 表名;
Create SYNONYM同义词名FOR 表名@数据库链接名;
4.删除
注意:用户有createpublic database link 或者create database link 权限.
数据库连接
创建是很简单,但是在使用中后台却出现锁,查看这个锁的方法可以去console中看到或者查询数据库。其实这个锁倒不是最让人心烦的,而是每次使用dblink查询的时候,均会与远程数据库创建一个连接,dblink应该不会自动释放这个连接,如果是大量使用dblink查询,会造成web项目的连 接数不够,导致系统无法正常运行。
1 SQL> show parameter open_links;2 3 NAME TYPE VALUE4 ------------------------------------ ----------- ------------------------------5 open_links integer46 open_links_per_instance integer4
解决方法: 修改连接数
alter system set open_links=6scope=spfile;