oracle database link

目前我的数据库里只有tinadb一个实例，要创建db link 需要再创建一个库， 
因为主机资源有限，我就在本地创建了。db link也同样适用于连接远程数据库，只需要对应ip即可 

1.dbca新建库sab 

配置监听 

   (DESCRIPTION_LIST = 
       (DESCRIPTION = 
          (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.12.253)(PORT = 1521)) 
        ) 
   ) 
SID_LIST_tinadb = 
    (SID_LIST = 
       (SID_DESC = 
          (GLOBAL_DBNAME= tinadb) 
          (SID_NAME = tinadb) 
          (ORACLE_HOME = /u01/oracle) 

   (DESCRIPTION_LIST = 
       (DESCRIPTION = 
          (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.12.253)(PORT = 1522)) --端口不能重复 

伦理片 http://www.dotdy.com/

SID_LIST_sara = 
    (SID_LIST = 
       (SID_DESC = 
          (GLOBAL_DBNAME= sab) 
          (SID_NAME = sab) 
          (ORACLE_HOME = /u01/oracle) 
        ) 
     ) 

启动监听lsnrctl start sab 

2.database link作用 
  database link，它是为了更方便的在一个数据库中访问另一个数据库（包括本地和远程的，道理是一样的）而创建的一个连接字符串。 

3.授权用户创建db link 

1）如果是用sys用户或者拥有dba角色的用户，就不需要授权 
SQL>  select ROLE,PRIVILEGE,ADMIN_OPTION from role_sys_privs where role='DBA' and PRIVILEGE like '%LINK%'; 
ROLE        PRIVILEGE ADM 
------------------------------ ---------------------------------------- --- 
DBA        DROP PUBLIC DATABASE LINK YES 
DBA        CREATE DATABASE LINK YES 
DBA        CREATE PUBLIC DATABASE LINK YES 

2）普通用户:grant create public database link,create database link to username;  

4.创建db link 

1）我们先在sara库中创建一个db link 连接到tinadb库 

create public database link "lk_sab_tinadb" connect to "tina" identified by "tina" 
using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.12.253)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME =tinadb)))'; 

   验证db link的连通性： 
   select 'x' from dual@lk_sab_tinadb; 

2）查看db link 
SQL> select owner,object_name from dba_objects where object_type='DATABASE LINK'; 
OWNER    OBJECT_NAME 
----     ------------------- 
PUBLIC   LK_SAB_TINADB 

或者 
select * from dba_db_links; 

3）使用db link 
SQL> select * from tina.t1@lk_sara_tinadb; 
ID 
---------- 
1 
2 

SQL> create table ts as select * from tina.t1@lk_sab_tinadb; 
SQL> select * from ts; 
ID 
---------- 
1 
2 

4）我们也可以建立一个私有的db link，这次我们在tinadb中创建一个，连接到sab库中(用户suser是sab库中用户，表s1是sab中suser的表） 
[oracle@oratest admin]$ sqlplus sys/tina@tinadb as sysdba 
SQL> conn tina/tina@tinadb 
Connected. 
SQL> create database link "lk_tinadb_sab" connect to "suser" identified by "suser" 
using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.12.253)(PORT = 1522)))(CONNECT_DATA =(SERVICE_NAME =sab)))'; 
  影音先锋电影 http://www.iskdy .com/
Database link created. 

SQL> select owner,object_name from dba_objects where object_type='DATABASE LINK'; 
OWNER       OBJECT_NAME 
-------     ----------------------- 
TINA        LK_TINADB_SAB 

SQL> select * from suser.s1@LK_TINADB_SAB; 

ID NAME 
---------- ---------- 
1 test 
2 haha 
3 heihei 

SQL> insert into suser.s1@lk_tinadb_sab values(4,'youyou'); 

1 row created. 

SQL> commit; 

Commit complete. 

SQL> select * from suser.s1;  ---sab库中就直接被修改了。 

ID NAME 
---------- ---------- 
4 youyou 
1 test