oracle dblink
当用户要跨本地数据库,访问另外一个数据库表中的数据时,本地数据库中必须创建了远程数据库的dblink,通过dblink本地数据库可以像访问本地数据库一样访问远程数据库表中的数据。
查询当前用户关于dblink的权限:
select * from user_sys_privs t where t.privilege like upper('%link%');
如果没有权限,进入sys、system用户授权:
grant CREATE PUBLIC DATABASE LINK,DROP PUBLIC DATABASE LINK to 用户名;
然后建dblink链接:
1、配置了本地服务名:
create [public] database link dblink名称
connect to bwtmp identified by bwtmp
using '本地服务名';
更改链接地址可以通过本地服务名客户端或者tnsnames.ora文件修改来实现。
2、没有配置了本地服务名:
create [public] database link dblink名称
connect to bwtmp identified by bwtmp
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.252.126)(PORT =1521))
)
(CONNECT_DATA =
(SERVICE_NAME = 数据库ssid字符串(例:orcl))
)
)';
更改链接地址可以通过sql语句修改表sys.link$来实现。
测试dblink:
select * from 用户名.表@dblink名称;
3、创建同义:
CREATE SYNONYM xxx FOR 用户名.表@dblink名称;
同义词创建后,我们以进行如下进行数据查询
select * from xxx;
4、删除同义词:
drop synonym xxx
5、查询所有已创建dblink:
select owner,object_name from dba_objects where object_type='DATABASE LINK';
或select * from ALL_DB_LINKS;
6、删除dblink:
删除当前用户dblink:
当前用户登录,drop database link dblink名称;
删除public dblink:
登录拥有权限用户(create public database link 或 create database link 权限),drop public database link dblink名称;
PS:
假如你想在本地库查询数据并跨库插入其他库,请注意:sql语句中,不能用到本地的数据库对象,常见的有sequence,function,view 等,如果要这么做,只能在pl/sql块中通过变量赋值后再传入sql。原因:在本地操作dblink指向数据库的sql语句,最终是发送到到远程数据库后再执行的,这样语句中包含的本地数据库对象已经失效了。所以建议:如果方便的话尽量从被插入、更新等端建立dblink(单向),向远端查询到本地再进行插入、更新等操作。