【练习】创建私有的dblink

 1.创建dblink第一种方法，是在本地数据库tnsnames.ora文件中配置了要远程访问的数据库。

1.设置监听：
①[root@host02 ~]# vi /etc/hosts
添加：【IP和名字】
192.168.206.2   host02
②vi listener.ora：
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = host02)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

③vi tnsnames.ora:
ENMO=
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = host02)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ENMO)
    )
  )
④查看监听状态：
[oracle@host02 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 01-DEC-2016 20:37:30

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host02)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                01-DEC-2016 20:18:58
Uptime                    0 days 0 hr. 18 min. 32 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/host02/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host02)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "ENMO" has 1 instance(s).
  Instance "ENMO", status READY, has 1 handler(s) for this service...
Service "ORCLXDB" has 1 instance(s).
  Instance "ENMO", status READY, has 1 handler(s) for this service...
The command completed successfully

⑤连接到sys赋权限：
SQL> conn / as sysdba
Connected.
SQL> grant create public database link,create database link to scott;

Grant succeeded.
⑥创建dblink：
SQL> create database link emp connect to scott identified by tiger using host02；

Database link created.

 

2.创建dblink第二种方法，是在本地数据库tnsnames.ora文件中没配置要远程访问的数据库。

1.设置监听：
①[root@host02 ~]# vi /etc/hosts
添加：【IP和名字】
192.168.206.2   host02
②vi listener.ora：
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = host02)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

③vi tnsnames.ora:
ENMO=
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = host02)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ENMO)
    )
  )
④查看监听状态：
[oracle@host02 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 01-DEC-2016 20:37:30

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host02)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                01-DEC-2016 20:18:58
Uptime                    0 days 0 hr. 18 min. 32 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/host02/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host02)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "ENMO" has 1 instance(s).
  Instance "ENMO", status READY, has 1 handler(s) for this service...
Service "ORCLXDB" has 1 instance(s).
  Instance "ENMO", status READY, has 1 handler(s) for this service...
The command completed successfully

⑤连接到sys赋权限：
SQL> conn / as sysdba
Connected.
SQL> grant create public database link,create database link to scott;

Grant succeeded.
⑥创建dblink：
SQL> create database link emp connect to scott identified by tiger using 
  2  '(DESCRIPTION =
  3      (ADDRESS = (PROTOCOL = TCP)(HOST = host02)(PORT = 1521))
  4      (CONNECT_DATA =
  5        (SERVER = DEDICATED)
  6        (SERVICE_NAME = ORCL)
  7      )
  8    )
  9  ';

Database link created.

3.创建公共的dblink
语法：create public database link emp using 。。。