oracle - dblink

1.同一个数据库的用户(a)要用到用户(b)的表等，所需要建立的连接

CCPBS_19 =    --SID引用名
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.130.38.19)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = CCPBS) -- 数据库实例名SID
    )
  )

格式：create public database link  link_name connect to  username(b)   identified by password  using 'SID引用名';

例子：create public database link  ecbm_19   connect to  ecbm_userName identified by ecbm_pwd  using 'CCPBS_19';

 

2.不同数据库之间的访问

create database link test_DBLINK 
  connect to username identified by "password "
    using '(DESCRIPTION = 
    (ADDRESS_LIST = 
    (ADDRESS = (PROTOCOL = TCP)(HOST = xx.xx.xx.xx)(PORT = 1521)) 
    ) 
    (CONNECT_DATA = 
    (SERVICE_NAME = orcl) 
    ) 
    )';

 

查询  select * from tablename@link_name;

如    select * from emp@ecbm_19;

删除 DROP PUBLIC DATABASE LINK ecbm_19;

--查询刚刚新建的dblink

select * from all_db_links order by created desc;