oracle dblink使用

1、查看dblink:

select * from dba_db_links

2、创建dblink的用户需要有创建dblink的权限，授权语句：(public是可选的)

grant create [public] database link to myAccount;

3、创建语句：

create public database link dblinkname 
connect to username identified by password
　　   using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = database_ip)(PORT = 1521)))
                            (CONNECT_DATA =(SERVICE_NAME =servicename))
               )';

 加上faliover等参数的情况：

create database link MCITY_DBLINK 
　　 connect to fsr identified by fsr 
　　 using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = database_ip)(PORT = 1521))
                                           (ADDRESS = (PROTOCOL = TCP)(HOST = database_ip)(PORT = 1521))
                                           (FAILOVER=yes) (LOAD_BALANCE =off))
                           (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = service_name))
                           (FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC)(RETIRES = 10) (DELAY = 15))
            )';

4、使用dblink：

   (1)直接查询：

select * from table@dblinkname

 table为远程目标数据库username下的表名

  (2)在本地创建同义词：

create or replace synonym synonymname table@dblinkname

synonymname是要创建的同义词名称，table为远程目标数据库username下的表名
然后用同义词查询：

select * from synonymname

  效果跟(1)一样

5、删除dblink和synonym：(public是可选的)

drop [public] database link dblinkname;

drop [public] synonym table_name;