oracle- create dblink

 

--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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22193071/viewspace-1129421/