ITPub博客

首页 > 数据库 > Oracle > oracle- create dblink

oracle- create dblink

原创 Oracle 作者:zuoqi_71 时间:2014-03-25 20:10:41 0 删除 编辑

 

--20140325 withus-dev add dblink

--1、prepare

set lines 200             
col db_link format a20    
col host format a20       

select * from dba_db_links;      


OWNER                          DB_LINK              USERNAME                       HOST                 CREATED
------------------------------ -------------------- ------------------------------ -------------------- ------------------
SYS                            EPRO_D               EPROCUSR                       EPROCDB              24-SEP-12
SYS                            EPROCUSR.EPRO_D      EPROCUSR                       EPROCDB              24-SEP-12


User Permition
select * from dba_sys_privs where grantee='EPROCUSR';          #create view&unlimited tablespace
select * from dba_sys_privs where grantee='EPROCIF';          #unlimited tablespace
select * from dba_sys_privs where grantee='EPROCSTA';         #create view&?unlimited tablespace

select * from dba_role_privs where grantee='EPROCUSR';         #RESOURCE,CONNECT,PLUSTRACE
select * from dba_role_privs where grantee='EPROCIF'           #RESOURCE,CONNECT
select * from dba_role_privs where grantee='EPROCSTA'          #RESOURCE,CONNECT


--2、create dblink
--2.1 add tnsname in $ORACLE_HOME/network/admin/tnsname.ora


EPROCDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = XXXX)(PORT = 1523))
    (CONNECT_DATA =
      (SERVICE_NAME = EPROCDB)
    )
  )


--2.2 grant privs by sys

grant create database link to EPROCUSR,EPROCIF,EPROCSTA;


--2.3 create dblink by EPROCUSR,EPROCIF,EPROCSTA

conn EPROCUSR/xxxx

CREATE DATABASE LINK USR_LINK
 CONNECT TO EPROCUSR IDENTIFIED BY "xxxx"
 USING 'EPROCDB';
 
conn EPROCIF/xxxx

CREATE DATABASE LINK IF_LINK
 CONNECT TO EPROCIF EPROCIF BY "xxxx"
 USING 'EPROCDB';
 
conn EPROCSTA/xxxx)

CREATE DATABASE LINK  STA_LINK
 CONNECT TO EPROCSTA IDENTIFIED BY "xxxx"
 USING 'EPROCDB';
 
--2.4 Verification
-- IF GET THIS VALUES IS OK
-- AT XXXX-DEV


conn EPROCUSR/xxxx

select count(*) from ESMJBMA;

COUNT(*)
---------
239


select count(*) from ESMJBMA@USR_LINK;

COUNT(*)
---------
132

 

 
 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22193071/viewspace-1129421/,如需转载,请注明出处,否则将追究法律责任。

上一篇: oracle-hot backup
请登录后发表评论 登录
全部评论

注册时间:2014-02-26

  • 博文量
    48
  • 访问量
    144670