【监听】dblink

创建dblink操作

客户端：192.168.10.3

 

服务端：192.168.10.2

 

前提：客户端想要查找服务端的hr用户下的ft表

SQL> select * from tab;

 

TNAME                          TABTYPE  CLUSTERID

------------------------------ ------- ----------

COUNTRIES                      TABLE

DEPARTMENTS                    TABLE

EMPLOYEES                      TABLE

EMP_DETAILS_VIEW               VIEW

FT                             TABLE

JOBS                           TABLE

JOB_HISTORY                    TABLE

LOCATIONS                      TABLE

REGIONS                        TABLE

 

9 rows selected.

 

1） 首先检查客户端/服务端的监听是否已经启动：

  服务端：

oracle@wang ~]$ lsnrctl status

 

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 23-SEP-2016 16:42:59

 

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

 

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=wang)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production

Start Date                23-SEP-2016 16:30:32

Uptime                    0 days 0 hr. 12 min. 27 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

Listener Log File         /u01/app/oracle/diag/tnslsnr/wang/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=wang)(PORT=1521)))

Services Summary...

Service "ORA11GR2" has 2 instance(s).

  Instance "ORA11GR2", status UNKNOWN, has 1 handler(s) for this service...

  Instance "ORA11GR2", status READY, has 1 handler(s) for this service...

Service "ORA11GR2XDB" has 1 instance(s).

  Instance "ORA11GR2", status READY, has 1 handler(s) for this service...

The command completed successfully

[oracle@wang ~]$

 

客户端：

[oracle@bing ~]$ lsnrctl status

 

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 23-SEP-2016 16:44:34

 

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

 

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=bing)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production

Start Date                23-SEP-2016 16:29:27

Uptime                    0 days 0 hr. 15 min. 6 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

Listener Log File         /u01/app/oracle/diag/tnslsnr/bing/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=bing)(PORT=1521)))

Services Summary...

Service "PROD" has 1 instance(s).

  Instance "PROD", status READY, has 1 handler(s) for this service...

Service "PRODXDB" has 1 instance(s).

  Instance "PROD", status READY, has 1 handler(s) for this service...

The command completed successfully

[oracle@bing ~]$

 

2） 在客户端设置tnsname.ora(对服务端网络识别的信息及别名)

[oracle@bing dbs]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/

[oracle@bing admin]$ ls

listener.ora  samples  shrept.lst  tnsnames.ora

[oracle@bing admin]$

[oracle@bing admin]$ vi tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

 

11 =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.2)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = ORA11GR2)

    )

  )

 

~

"tnsnames.ora" 22L, 509C written                                                                                  

[oracle@bing admin]$

 

测试：

[oracle@bing admin]$ tnsping 11

 

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 23-SEP-2016 17:11:41

 

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

 

Used parameter files:

 

 

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.2)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORA11GR2)))

OK (10 msec)

[oracle@bing admin]$

 

3） 在客户端的数据库中创建DBlink：

SQL> create database link ftlink connect to hr identified by hr using '11';

 

Database link created.

可以将连接字符串'11'换为简便连接的ip:port/service_name(服务端的)。

SQL>

 

4）  在客户端的数据库内访问服务器端的hr用户下的ft表：

SQL> select count(*) from hr.ft@ftlink;

 

  COUNT(*)

----------

       107

 

验证：

SQL>  select instance_name,host_name from v$instance;

 

INSTANCE_NAME    HOST_NAME

---------------- ----------

PROD             bing

 

 

SQL> desc dba_db_links

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 OWNER                                     NOT NULL VARCHAR2(30)

 DB_LINK                                   NOT NULL VARCHAR2(128)

 USERNAME                                           VARCHAR2(30)

 HOST                                               VARCHAR2(2000)

 CREATED                                   NOT NULL DATE

 

SQL> select OWNER,DB_LINK,USERNAME,HOST,CREATED from dba_db_links;

 

OWNER DB_LINK  USERNAME HOST     CREATED

----- -------- -------- -------- ------------------------------------------------

SYS   FTLINK   HR       11       23-SEP-16

 

 

总结：DBlink就是登陆到本地数据库，可以以dblink的方式访问其他数据库用户下的信息的一种网络间通信方法。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31397003/viewspace-2126145/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31397003/viewspace-2126145/