oracle create dblink

2014-11-04

oracle database link是定义一个数据库到另一个数据库的路径的对象，database link允许你查询远程表,可实现通过远程表恢复本地记录。

1、select * from user_db_links;
select * from dba_db_links;

2、编辑$ORACLE_HOME/network/admin/tnsnames.ora，添加
DB02 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db02)
)
)
3、创建dblink所需的权限
grant dba to user01;
创建dblink所需的权限
Privilege Database Required For
CREATE DATABASE LINK Local Creation of a private database link.
CREATE PUBLIC DATABASE LINK Local Creation of a public database link.
CREATE SESSION Remote Creation of any type of database link.

4、dblink 分类
Private 创建database link的user拥有该database link
在本地数据库的特定的schema下建立的database link。只有建立该database link的schema的session能使用这个database link来访问远程的数据库。同时也只有Owner能删除它自己的private database link。

Public Owner是PUBLIC.
Public的database link是数据库级的，本地数据库中所有的拥有数据库访问权限的用户或pl/sql程序都能使用此database link来访问相应的远程数据库。

Global Owner是PUBLIC.
Global的database link是网络级的，When an Oracle network uses a directory server, the directory server automatically create and manages global database links (as net service names) for every Oracle Database in the network. Users and PL/SQL subprograms in any database can use a global link to access objects in the corresponding remote database.
Note: In earlier releases of Oracle Database, a global database link referred to a database link that was registered with an Oracle Names server. The use of an Oracle Names server has been deprecated. In this document, global database links refer to the use of net service names from the directory server.

5、create dblink
conn user01/password
create database link crm02 connect to crm identified by 123456 using 'DB02';
或者
create public database link crm02 connect to crm identified by 123456 using 'DB02';

CREATE DATABASE LINK "DBlink1" CONNECT TO "user01" IDENTIFIED BY VALUES '123456' USING '172.10.1.70:1521/dydb'

6、校验
select * from tab@crm02;
select * from dual@crm02;
7、dblink创建后字典表dba_db_link中只保存创建连接时使用用户名，密码不保存，实际上在sys用户下的另一张字典表link$中保存了明文的用户密码，如果出现密码丢失或者不确认的情况，可以从中获取明文密码。
select * from link$;

8、恢复数据实例
insert into t1 select * from t1@DB01 where cid='${cid}' and id=${id};

分类：数据库 | 标签： |

相关日志

评论被关闭！