Oracle之dblink信息详细查询和配置过程
- Oracle之dblink信息详细查询和配置过程
- Oracle中dblink概述
- Oracle中dblink信息详细查询
- Oracle中dblink的创建和检测
- Oracle中dblink的删除
Oracle之dblink信息详细查询和配置过程
本文总结Oracle中的dblink的使用方法，包括了查看、创建、删除等，具体内容如下：
Oracle中dblink概述
dblink(Database Link)数据库链接顾名思义就是数据库的链接，就像电话线一样，是一个通道，当我们要跨本地数据库，访问另外一个数据库表中的数据时，本地数据库中就必须要创建远程数据库的dblink,通过dblink本地数据库可以像访问本地数据库一样访问远程数据库表中的数据。
使用例子如下：
select * from table@Link_name; //使用Link_name查询其他库的表信息Oracle中dblink信息详细查询
使用dba账号查询Oracle数据库中的所有dblink相关信息。
SQL> col OWNER for a10;
SQL> col USERNAME for a10;
SQL> col HOST for a80;
SQL> set linesize 160； //查询结果总的的行长宽度为160
SQL> col DB_LINK for a16;
SQL> set pagesize 100 //每隔100行为一页再显示列名
SQL> select * from dba_db_links;
OWNER DB_LINK USERNAME HOST CREATED
---------- ---------------- ---------- -------------------------------------------------------------------------------- ---------
PUBLIC LINK_WLANDB WLAN_GZ wlandb 08-JUL-20
PUBLIC CRM_RPT_LINK TEST rpttest980 20-OCT-15
PUBLIC LINK_SHENGCHAN30 DBQUERY (DESCRIPTION = 12-APR-14
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.16.9.12 )(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = crmdb30)
(INSTANCE_NAME = crmdb301)
)
)
PUBLIC LINK_RZ DBQUERY (DESCRIPTION = 14-OCT-13
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.16.37.12)(PORT = 1521))
)
(CONNECT_DATA =
(SID = crm)
(SERVER = DEDICATED)
)
)
DBSALEADM TO_OA BOSS_LINK (DESCRIPTION = 23-MAR-15
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.16.35.43)(PORT = 1521))
)
(CONNECT_DATA =
(SID = oatest)
(SERVER = DEDICATED)
)
)
DBTRANS LINK_QUERY DBQUERY (DESCRIPTION = 26-APR-14
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.16.9.12 )(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = crmdb30)
(INSTANCE_NAME = crmdb301)
)
)
PRMUSER VASSP VASSP (DESCRIPTION = 17-OCT-13
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.16.9.69)(PORT = 1521))
)
(CONNECT_DATA =
(SID = gdvassp)
(SERVER = DEDICATED)
)
)
PRMUSER RPTLNK DBQUERY gztvrpt 17-OCT-13
PRMUSER PRMTEST28 PRMUSER (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.16. 17-OCT-13
9.28)(PORT = 1521)) ) (CONNECT_DATA = (SID = gztvcrm) (SERVER =
DEDICATED) ) )
9 rows selected.使用非DBA账号登录显示能访问权限的dblink详细信息。
SQL> conn PRMUSER/******; //当前用户名为PRMUSER
Connected.
SQL>
SQL> select * from ALL_DB_LINKS;
OWNER DB_LINK USERNAME HOST CREATED
---------- ---------------- ---------- -------------------------------------------------------------------------------- ---------
PUBLIC LINK_RZ DBQUERY (DESCRIPTION = 14-OCT-13
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.16.37.12)(PORT = 1521))
)
(CONNECT_DATA =
(SID = crm)
(SERVER = DEDICATED)
)
)
PRMUSER PRMTEST28 PRMUSER (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.16. 17-OCT-13
9.28)(PORT = 1521)) ) (CONNECT_DATA = (SID = gztvcrm) (SERVER =
DEDICATED) ) )
PRMUSER RPTLNK DBQUERY gztvrpt 17-OCT-13
PRMUSER VASSP VASSP (DESCRIPTION = 17-OCT-13
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.16.9.69)(PORT = 1521))
)
(CONNECT_DATA =
(SID = gdvassp)
(SERVER = DEDICATED)
)
)
PUBLIC LINK_SHENGCHAN30 DBQUERY (DESCRIPTION = 12-APR-14
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.16.9.12 )(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = crmdb30)
(INSTANCE_NAME = crmdb301)
)
)
PUBLIC CRM_RPT_LINK TEST rpttest980 20-OCT-15
PUBLIC LINK_WLANDB WLAN_GZ wlandb 08-JUL-20
7 rows selected.从结果可知查询ALL_ DB_ LINKS能得到OWNER为PUBLIC和自己创建的DBLINK的详细信息，而DBA用户访问 dba_ db_links能得到整个数据库的所有BDLINK信息。
其中这两个表的OWNER列一般为PUBLIC、创建者本身用户，当为PUBLIC全局时每个用户都可以用这个DBLINK，为自身账号时仅本账号可用。DB_LINK列为名称。USERNAME列为远程数据库的连接账号名称。HOST列为远程数据库的连接信息。CREATED列为创建时间。
Oracle中dblink的创建和检测
语法：create public database link <DBLink名称> connect to <被连接库的用户名> identified by <被连接库的密码> using ‘<Oracle客户端工具建立的指向被连接库服务名，即TNSNAME中配置的本地服务名>’;
public指明所有用户均可使用这个DBlink,如果不指定，则只有创建者才能使用。
创建dblink的用户有对应的数据库权限：
grant create public database link, create database link to username;使用本地服务名来创建public dblink：
SQL> create public database link HUAWEI_LINK connect to icd IDENTIFIED BY "icd-1234" using 'hw';
Database link created
//创建public全局的dblink,除非授权不然只有DBA账号能创建public类型，名称为HUAWEI_LINK，使用了配置文件TNSNAMES.ORA中的hw不使用本地服务名创建，在创建dblink时加上description信息：
SQL> create database link TOEAS_LINK connect to gsuser IDENTIFIED BY gsuser2010
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.16.9.62)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = qicaidb)
)
)';
Database link created创建私有dblink时语句中不使用public即可，查询创建后是否正常方法为：
SQL> select * from dual@LINK_WLANDB; //显示为以下时则DBLINK创建成功
D
-
X
SQL> select sysdate from dual@LINK_WLANDB; //还可以检查远程数据库时间来检查
SYSDATE
---------
14-JUL-20Oracle中dblink的删除
SQL> drop database link linkname; //删除私有用户的DBLINK
SQL> drop public database link linkname; //删除全局DBLINK
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
