1、dblink的种类
私有dblink：私有dblink用create database link语句创建，私有dblink只能在该数据库中由创建该dblink的用户才能使用和删除，其他用户都不可以使用和删除。
举例： create database link ts connect to user identified by mima using ' ora11g';
ora11g是在 /u01/app/oracle/product/11.2.0/dbhome_2/network/admin/ tnsnames.ora 这定义的服务
ora11g =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.5)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ora10g)
) )
公有 dblink：公有dblink用create public database link语句创建，只要是数据库里的用户都可以使用公有dblink，如果用户有删除dblink的权限， 那么就可以删除任何公有dblink。
举例： create publicdatabase link ts connect to user identified by "mima" using ' ora11g';
global dblink: 和共有私有不同的是global dblink创建不用直接在tnsnames.ora中定义，而是直接把服务写在dblink的定义中
create public database link ts connect to user identified by "mima" using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ip)(PORT = 1521))
) (CONNECT_DATA =
(SERVICE_NAME = 服务名称)
))';
2、创建dblink需要的权限
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. |
通过下面的语句可以查看，当前用户拥有创建dblink的那些权限
SELECT DISTINCT PRIVILEGE AS "Database Link Privileges" FROM ROLE_SYS_PRIVS WHERE PRIVILEGE IN ( 'CREATE SESSION','CREATE DATABASE LINK', 'CREATE PUBLIC DATABASE LINK')
建立database link之前需要确认的事项：
- 如果using使用，那么l本地数据库到remote database的网络连接是正常的，tnsping要能成功。远端的监听正常启动
- 确认在remote database上面有相应的访问权限，用户名，密码正确。
3、创建dblink
4、dblink按照连接用户划分
| 指明连接用户的方式 | 私有link | 共有link | 解释 |
| fix user | create database link ts connect to user identified by "mima" using 'net_link'; | create public database link ts connect to user identified by "mima" using 'net_link'; | fix user link是指，所有使用此link的用户必须使用建立link时指定 connect user和密码 去连接对方数据库 |
| current_user | create database link ts connect to current_user using 'net_link'; | create public database link ts connect to current_user using 'net_link'; | 使用当前用户 |
| connect user | create database link ts using 'net_link' | create public database link ts using 'net_link' | 使用当前登录的用户的用户名和密码去连接对方数据库相同的用户 |
fix user link:好用的就是fixed user,创建时指定连接远程数据库的用户名和密码并存在数据字典里。以后所有的使用dblink 的用户实际上都是使用这个用户名和密码来登录到远程数据库的，在远程数据库的权限和这个用户一样
connect user 实验：
ora11g数据库：
SQL> create public database link ts using 'ora11g';
SQL> grant dba to u1;
SQL> create table t1 as select * From scott.dept;
Table created.
ora10g数据库：
SQL> create user u1 identified by u1 account unlock;
SQL> grant dba to u1;
SQL> create public database link ts using 'ora11g';
conn u1/u1
SQL> select * From t1@ts;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
conn scott/mima
SQL> select * From dept@ts;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
ora11g数据库：
SQL> grant select on t1 to scott;
Grant succeeded.
SQL> conn scott/tiger
Connected.
SQL> select * from u1.t1;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
ora10g数据库：
SQL> show user
USER is "SCOTT"
SQL> select * From u1.t1@ts;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
结论：
（1）connect user 方式创建dblink，其连接对方数据库的方式是：用登录数据库的用户名和密码去登录对方数据库，说明两边数据库的用户名和密码必须一致，才能用connect user方式，否则会报错
（2）公用dblink可以给多用户使用，只要两端数据库的用户名和密码一样，都可以用除sys用户名外的用户验证，私有dblink只能给一个用户使用，那就是创建link的用户。
（3）如果dblink连接用户名和密码都通过对方数据库的验证，那么可以拥有对方数据库相同用户的查询权限
current user 方式的link，暂时实验不通，有待进一步研究
5、share link：
In a shared database link, multiple sessions in the source database share the same connection to the target database. Once a session is established on the target database, that session is disassociated from the connection, to make the connection available to another session on the source database. To prevent an unauthorized session from attempting to connect through the database link, when you specifySHARED you must also specify thedblink_authentication clause for the users authorized to use the database link.
以share 方式创建dlbink，那么源端的数据库的多个session可以共享目标端的同一个连接。一旦某个session在目标端数据库建立连接后，就会断开，目的是为了让源端的session可以使用，以share方式建立起的link，为了阻止未授权的session试图通过dblink去连接目标端数据库，你可以指定dblink authentication子句让用户验证使用dblink
共享数据库链接是指该链接的多个用户可以共享同一个底层网络连接。
在创建share link的时候如果不指定public，那么默认创建的是私有 share dblink，如果指定了public，那么创建的是public dblink。
SQL> create shared database link ss AUTHENTICATED by scott identified by "tiger" using 'ora11g';
SQL> select * From dba_db_links;
OWNER DB_LINK USERNAME HOST CREATED
------------------------------ ---------- ------------------------------ -------------------------------------------------- ---------
SCOTT SS ora11g 09-JUN-13
6、 给dblink起别名
-- 不想让使用的人知道database link的名字的时候
-- 建一个别名包装一下
CREATE SYNONYM table_name FOR table_name@database link;
SELECT * FROM table_name;
-- 或者，也可以建立一个视图来封装
CREATE VIEW table_name AS SELECT * FROM table_name@database link;
7、dblink也是一个事物
select * from v$transaction；-- 没有记录，说明没有事务
-- 通过database link连接远程数据库，select 其中一个表
select * from bbs_news@mylink;
select * from v$transaction;-- 发现有一条记录。
因为本地数据库只是将对应的sql发送给远程数据库执行，接受remote db返回的结果，但他并不知道是否该sql修改了数据； 所以需要为select 语句也标示一个事务。具体可以参考otn 分布式数据库手册，所以在用database link远程访问时,要加上set transaction read only
8、查看global_name
SQL> show parameter global_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_names boolean FALSE
如果global_name=true,那么你创建的dblink名称要和对方数据库的globalname名称一样，否则，建立的dblink会报错。如果global_name=false ,那么你的dblink名称可以随意建立
具体可以参考http://blog.csdn.net/wll_1017/article/details/8991742
9、关闭dblink
ALTER SESSION CLOSE database link sales;
10、通过数据库链执行DDL语句
- 不能通过database link提交事务commit. 不能执行类似commit@remote_site ;只能在初始化事务的站点执行提交。
- 不能通过database link执行DDL语句。DDL被Oracle理解为Begin commit;exec DDL; End; 既然不能通过database link提交事务commit，也就不能运行DDL．
- 不能通过database link设置SAVEPOINT．总之，所有的事务控制语句（commit rollback savepoint set transaction etc）都不能通过database link执行。
我们知道，可以通过数据库链进行DML操作，但是所有的DDL操作都被禁止。但是幸运的是，可以通过调用远端存储过程的方式在远端站点上执行DDL操作。
简单举例如下：
SQL> conn yangtk/yangtk@yangtk
已连接。
SQL> create or replace procedure p_execute_ddl(p_ddl in varchar2)
2 as
3 begin
4 execute immediate p_ddl;
5 end;
6 /
过程已创建。
SQL> exec p_execute_ddl('create table aaa (id number)');
PL/SQL 过程已成功完成。
SQL> desc aaa
名称 是否为空? 类型
------------------------------------ -------- --------
ID NUMBER
SQL> conn yangtk/yangtk@test
已连接。
SQL> create database link yangtk.yangtingkun connect to yangtk identified by yangtk using 'yangtk';
数据库链接已创建。
SQL> drop table aaa@yangtk;
drop table aaa@yangtk
*
ERROR 位于第 1 行:
ORA-02021: 不允许对远程数据库进行 DDL 操作
SQL> exec p_execute_ddl@yangtk('drop table aaa');
PL/SQL 过程已成功完成。
SQL> select * from tab@yangtk where tname = 'AAA';
未选定行
需要注意的是，由于ddl的执行是在存储过程中，任何通过角色授予的权限都是无效的，必须明确授予权限。
当然，你也可以通过建立调用者权限的存储过程来使用角色中已经具有的权限。
参考：http://yangtingkun.itpub.net/post/468/8699
Step 1) Create procedure on Remote Site.It uses dbms_job to sumbit a ddl job. Note: dbms_job is transactional.
CREATE OR REPLACE PROCEDURE
create_Remote_Table(p_table_name VARCHAR2) AS
job number;
BEGIN
dbms_job.submit(job,CREATE TABLE ' || p_table_name ||' (c1 number)');
END create_Remote_Table;
/
Step 2) Call the remote procedure on Local Site.
SQL> exec create_Remote_Table('t')@db_link;
PL/SQL procedure successfully completed.
Step 3) On remote site , select * from user_jobs;
SQL> select what from user_jobs;
WHAT
--------------------------------------------
create table t(c1 number);
Step 4) On Local site , commit or rollback transaction will make the remote job run or cancel.
参考： http://dbaoracle.itpub.net/post/901/52542
注意：
在创建数据库连接的时候需要注意些问题：
(1)密码要用双引号引起来。否则有可能会报错。
例如：create public database link tt connect to scott identified by "tiger" using 'ora11g'
SQL> select * from scott.t1@tt;
select * from scott.t1@tt
*
ERROR at line 1:
ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from TT
（2）注意区别共有dblink和私有dblink，下面两个dblink ，sys用户的，一定要用sys用户使用dblink，如果scott用户连接，也会报错的。public dblink是哪个一个用户都可以使用。删除的时候同样，私有dblink一定要用制定的用户去删除。
SQL> create database link ts connect to scott identified by "tiger" using 'ora11g';
Database link created.
SQL> select * from dba_db_links;
OWNER DB_LINK USERNAME HOST CREATED
------------------------------ -------------------- ------------------------------ -------------------------------------------------- -------------------
PUBLIC TS SCOTT ora11g 2013-06-03 22:06:35
SYS TS SCOTT ora11g 2013-06-03 22:29:38
参考：
http://docs.oracle.com/cd/E11882_01/server.112/e25494/ds_admin002.htm#ADMIN12150
http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_5005.htm#SQLRF01205
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
