--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/