DBlink主要是简易实现数据的迁移，
以下是一些创建dblink的方法。

--客户机数据库信息：
SQL> show parameter service
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string ORA11GR2

--服务端配置：
[oracle@enmo admin]$ vi listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = enmo)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

--客户端配置：
[oracle@host01 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.

ORA11GR2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.6)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PROD)
)
)

--客户端sys用户创建独享dblink:
SQL> create database link dl88 connect to scott identified by tiger
2 using 'ora11gr2';
Database link created.

--查询连接到的用户的数据表：
SQL> select empno,ename from emp@dl88;
EMPNO ENAME
---------- ----------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
EMPNO ENAME
---------- ----------
7900 JAMES
7902 FORD
7934 MILLER
14 rows selected.

--直接用EZCONNECT创建独享DBlink：
SQL> create database link hl99 connect to hr identified by hr
2 using '192.168.2.6:1521/PROD';
Database link created.

--查询连接到的用户数据表：
SQL> select employee_id,last_name from employees@hl99;

EMPLOYEE_ID LAST_NAME
----------- -------------------------
174 Abel
166 Ande
130 Atkinson
105 Austin
204 Baer
116 Baida
... ...
123 Vollman
196 Walsh
120 Weiss
200 Whalen
149 Zlotkey
107 rows selected.

--sys用户创建公共DBlink：
SQL> create public database link db_link connect to hr identified by hr
2 using 'ora11gr2';
Database link created.

--使用sky用户查询连接的用户表数据：
SQL> conn sky/sky
Connected.
SQL>
SQL> select count(*) from employees@db_link;
COUNT(*)
----------
107

--授权给其他用户创建独享DBlink：
SQL> grant create database link to sky;
Grant succeeded.

--在sky用户下创建DBlink：
SQL> create database link hl88 connect to hr identified by hr
2 using 'ora11gr2';
Database link created.

SQL> select count(*) from employees@hl88;

COUNT(*)
----------
107

--删除所创建的dblink：
SQL> drop database link hl88;
Database link dropped.

使用DBLINK，就是更加容易使用远程数据库到本地使用的客户机上，
简化数据迁移流程。

ITPub博客

DBLINK

最新文章