oracle dblink

理解公共链接(public database link)与链接(database link，只能当前schema访问)的不同.

Use the CREATE DATABASE LINK statement to create a database link. A database link is a schema object in one database that enables you to access objects on another database. The other database need not be an Oracle Database system. However, to access non-Oracle systems you must use Oracle Heterogeneous Services.

After you have created a database link, you can use it to refer to tables and views on the other database. In SQL statements, you can refer to a table or view on the other database by appending @dblink to the table or view name. You can query a table or view on the other database with the SELECT statement. You can also access remote tables and views using any INSERT, UPDATE, DELETE, or LOCK TABLE statement.

To create a private database link, you must have the CREATE DATABASE LINK system privilege. To create a public database link, you must have the CREATE PUBLIC DATABASE LINK system privilege. Also, you must have the CREATE SESSION system privilege on the remote Oracle database.

Oracle Net must be installed on both the local and remote Oracle databases.

配置本地tnsnames.ora文件，要包含远程访问
一个示例：
REMOTEOR = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = PC110729BJ002)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = remoteOra) ) ) ORA10G = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = ora10g) ) )
与dblink相关的权限：
CREATE DATABASE LINK
DROP PUBLIC DATABASE LINK
CREATE PUBLIC DATABASE LINK

测试dbalink的使用，并理解公共链接(public database link)与链接(database link，只能当前schema访问)的不同.

C:\Windows\system32>sqlplus sys/oracle@ora10g as sysdba SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 12月 22 15:51:27 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. 连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options SQL> create user laughing identified by laughing; 用户已创建。 --查看connect与resource有哪些权限 SQL> select * from role_sys_privs WHERE ROLE LIKE 'CONNECT'; ROLE PRIVILEGE ADM ------------------------------ ---------------------------------------- --- CONNECT CREATE SESSION NO SQL> select * from role_sys_privs WHERE ROLE LIKE 'RESOURCE'; ROLE PRIVILEGE ADM ------------------------------ ---------------------------------------- --- RESOURCE CREATE SEQUENCE NO RESOURCE CREATE TRIGGER NO RESOURCE CREATE CLUSTER NO RESOURCE CREATE PROCEDURE NO RESOURCE CREATE TYPE NO RESOURCE CREATE OPERATOR NO RESOURCE CREATE TABLE NO RESOURCE CREATE INDEXTYPE NO 已选择8行。 SQL> grant connect,resource to laughing; 授权成功。 SQL> conn laughing/laughing@ora10g 已连接。 SQL> conn sys/oracle@ora10g as sysdba 已连接。 SQL> conn laughing/laughing@ora10g 已连接。 SQL> create database link link_laughing connect to scott identified by tiger using 'REMOTEOR'; create database link link_laughing connect to scott identified by tiger using 'REMOTEOR' * 第 1 行出现错误: ORA-01031: 权限不足 SQL> conn sys/oracle@ora10g as sysdba 已连接。 SQL> grant create database link to laughing; 授权成功。 SQL> conn laughing/laughing@ora10g 已连接。 SQL> create database link link_laughing connect to scott identified by tiger using 'REMOTEOR'; 数据库链接已创建。 SQL> select count(*) from emp@link_laughing; COUNT(*) ---------- 14 SQL> drop database link link_laughing; 数据库链接已删除。 SQL> create public database link link_public connect to scott identified by tiger using 'REMOTEOR'; create public database link link_public connect to scott identified by tiger using 'REMOTEOR' * 第 1 行出现错误: ORA-01031: 权限不足 SQL> conn sys/oracle@ora10g as sysdba 已连接。 SQL> grant create public database link to laughing; 授权成功。 SQL> conn laughing/laughing@ora10g 已连接。 SQL> create public database link link_public connect to scott identified by tiger using 'REMOTEOR'; 数据库链接已创建。 SQL> select count(*) from emp@link_public; COUNT(*) ---------- 14 SQL> conn scott/tiger@ora10g 已连接。 SQL> select count(*) from emp@link_public; COUNT(*) ---------- 14 SQL> conn laughing/laughing@ora10g 已连接。 SQL> drop public database link link_public; drop public database link link_public * 第 1 行出现错误: ORA-01031: 权限不足 SQL> conn sys/oracle@ora10g as sysdba 已连接。 SQL> grant drop public database link to laughing; 授权成功。 SQL> conn laughing/laughing@ora10g 已连接。 SQL> drop public database link_public; drop public database link_public * 第 1 行出现错误: ORA-02026: 缺失 LINK 关键字 SQL> drop public database link link_public; 数据库链接已删除。 SQL> select * from user_sys_privs where privilege like upper('%DATABASE LINK%'); USERNAME PRIVILEGE ADM ------------------------------ ---------------------------------------- --- LAUGHING CREATE DATABASE LINK NO LAUGHING DROP PUBLIC DATABASE LINK NO LAUGHING CREATE PUBLIC DATABASE LINK NO
为远程链接创建同义词

--创建同义词。 SQL> conn laughing/laughing@ora10g 已连接。 SQL> create SYNONYM emp_table for emp@link_laughing; create SYNONYM emp_table for emp@link_laughing * 第 1 行出现错误: ORA-01031: 权限不足 SQL> conn sys/oracle@ora10g as sysdba 已连接。 SQL> grant create sysnonym to laughing; grant create sysnonym to laughing * 第 1 行出现错误: ORA-00990: 权限缺失或无效 SQL> grant create synonym to laughing; 授权成功。 SQL> conn laughing/laughing@ora10g 已连接。 SQL> create SYNONYM emp_table for emp@link_laughing; 同义词已创建。 SQL> select count(*) from emp_table; COUNT(*) ---------- 14 SQL> conn scott/tiger@ora10g 已连接。 SQL> select count(*) from emp@link_laughing; select count(*) from emp@link_laughing * 第 1 行出现错误: ORA-02019: 未找到远程数据库的连接说明 SQL> select count(*) from laughing.emp_table; select count(*) from laughing.emp_table * 第 1 行出现错误: ORA-02019: 未找到远程数据库的连接说明 SQL> grant select on emp_table to scott; grant select on emp_table to scott * 第 1 行出现错误: ORA-02021: 不允许对远程数据库进行 DDL 操作
--创建同义词 SQL> create synonym emp_public for emp@link_public; 同义词已创建。 SQL> select count(*) from emp_public; COUNT(*) ---------- 14 SQL> conn scott/tiger@ora10g 已连接。 SQL> select count(*) from emp_public; select count(*) from emp_public * 第 1 行出现错误: ORA-00942: 表或视图不存在 SQL> select count(*) from emp@link_public; COUNT(*) ---------- 14 SQL> select count(*) from laughing.emp_public; COUNT(*) ---------- 14
参考：
http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5005.htm