本文实例讲述了oracle跨库查询dblink的用法。分享给大家供大家参考,具体如下:

当用户要跨本地数据库,访问另外一个数据库表中的数据时,本地数据库中必须创建了远程数据库的dblink,通过dblink本地数据库可以像访问本地数据库一样访问远程数据库表中的数据。本文主要介绍关于dblink的常见操作和相关的一点oracle操作。

1.创建之前的工作

   在创建dblink之前,首先要查看用户是否有相应的权限。针对特定的用户,使用
sqlplus user/pwd登录后,执行如下语句:

在创建dblink之前,首先要查看用户是否有相应的权限。针对特定的用户,使用
sqlplus user/pwd登录后,执行如下语句:复制代码 代码如下:select * from
user_sys_privs t where t.privilege like
upper(‘%link%’);在sys用户下,显示结果为:

select * from user_sys_privs
t where t.privilege likeupper(‘ style=”margin: 0px; padding: 0px; max-width: 100%; color: rgb(139, 0, 0);”>%link%’);

SYS CREATE DATABASE LINK NOSYS DROP PUBLIC DATABASE LINK NOSYS CREATE
PUBLIC DATABASE LINK NO

在sys用户下,显示结果为:

可以看出在数据库中dblink有三种权限:

SYS
CREATE DATABASE LINK NO

CREATE DATABASE
LINK–所创建的dblink只能是创建者能使用,别的用户使用不了CREATE PUBLIC
DATABASE LINK–public表示所创建的dblink所有用户都可以使用DROP PUBLIC
DATABASE LINK–删除指定dblink

SYS
DROP PUBLIC DATABASE LINK NO

如果想要改变某个用户的权限,需要在sys用户下修改:复制代码 代码如下:grant CREATE PUBLIC DATABASE
LINK,DROP PUBLIC DATABASE LINK to
scott;查看dblink,有两种方式,分别如下:

SYS
CREATE PUBLIC DATABASE LINK NO

①.复制代码 代码如下:select
owner,object_name from dba_objects where object_type=’DATABASE
LINK’;②.复制代码 代码如下:select * from
dba_db_links;

可以看出在数据库中dblink有三种权限:

2. 创建dblink

CREATE
DATABASE LINK–所创建的dblink只能是创建者能使用,别的用户使用不了

create public database link LINK_NAMEconnect to USRNAME identified by "PASSWORD"using '(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = XXX.XXX.XXX.XXX)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = XXX)) )';

CREATE
PUBLIC DATABASE LINK–public表示所创建的dblink所有用户都可以使用

注意:using后跟的是一个字符串,其中一定不要出现不必要的空格,否则会出错ORA-12514,在上面的代码中为了方便阅读其中进行了换行,可能会出现空格而导致错误,所以使用的时候将空格去掉就ok了。

DROP
PUBLIC DATABASE LINK–删除指定dblink

这里LINK_NAM为自定的名称;USERNAME和PASSWORD为指定的oracle数据库中的用户名和密码,SERVICE_NAME如果不确定的话,可以通过以下语句获得:复制代码 代码如下:show parameter
service_names;或者复制代码
代码如下:select name,value from v$parameter where
name=’service_names’3.dblink的使用

 
 如果想要改变某个用户的权限,需要在sys用户下修改:

dblink的使用相对比较简单,把一般访问本地表时的表名改为如下格式即可:[user.]table@link_name。复制代码 代码如下:select studentid from
abc.studeng@abc_ten;4.删除dblink

grantCREATEPUBLICDATABASE LINK,DROPPUBLICDATABASE LINK to scott;

确定要删除的dblink名字以后,可以通过drop命令直接将其删除:复制代码 代码如下:drop public database link
abc_ten;

 确定具备了相应的权限以后,登录sqlplus即可对dblink进行相应的操作了。

   1. 查看dblink,有两种方式,分别如下:

希望本文所述对大家Oracle数据库程序设计有所帮助。

select owner,object_name from dba_objects where object_type=’ style=”margin: 0px; padding: 0px; max-width: 100%; color: rgb(139, 0, 0);”>DATABASE
LINK’;

select * from dba_db_links;

    2.
创建dblink,也有两种方式。

 
 第一种是使用tnsnames.ora配置文件添加相关配置信息,形如:

chose_you_like =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xxx.xxx)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = yourOracleServiceName)

    )

  )

然后在sqlplus终端下执行:

createpublicdatabase link
link_name connecttouser identified by pwd using ‘ style=”margin: 0px; padding: 0px; max-width: 100%; color: rgb(139, 0, 0);”>chose_you_like’;

   
第二种是直接在sql语句中使用配置信息,语法格式如下:

createpublicdatabase link link_name connecttouser identified by pwd

using

'(DESCRIPTION =

    (ADDRESS_LIST =

    (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xxx.xxx)(PORT = 1521))

  )

  (CONNECT_DATA =

    (SERVER = DEDICATED)

    (SERVICE_NAME = yourOracleServerName)

  )

 )';

这里的user和pwd为指定的oracle数据库中的用户名和密码,SERVICE_NAME如果不确定的话,可以通过以下语句获得:

show parameter service_names;

或者

select name,valuefrom v$parameter where name='service_names'

SERVICE_NAME的值必须与源数据库保持一致,否则dblink创建成功后访问时会报错,错误提示如下:

ORA-12514: TNS: listener
does not currently
know of service
requested inconnectdescriptor

3. 使用dblink

    dblink的使用相对比较简单,把一般访问本地表时的表名改为如下格式即可:[user.]table@link_name。例如,在本机数据库上创建了一个scott_dblink的public dblink(使用远程主机

的scott用户连接),则用sqlplus连接到本机数据库,执行select * from scott.emp@scott_rmthot即可以将远程数据库上的scott用户下的emp表中的数据获取到。也可以在本地建一个同义

词来指向scott.emp@scott_rmthost,方便日常的使用。

  4. 删除dblink

    确定要删除的dblink名字以后,可以通过drop命令直接将其删除:

droppublicdatabase link
dblinkname;

发表评论

电子邮件地址不会被公开。 必填项已用*标注

相关文章