oracle / db link

-- 해당유저에 db link생성권한부여

grant create database link           to 유저명;

grant create public database link     to 유저명;

grant drop public database link       to 유저명;

 

 

-- db link생성

--  public : 전체유저가 이용가능

--  생략시  해당 생성유저만 이용가능

create public database link ORACLE1_sms connect to "원격유저명" identified by "원격유저비번" using 'ORACLE1';

create           database link ORACLE1_sms connect to "원격유저명" identified by "원격유저비번" using 'ORACLE1';

==>ORACLE1 은  ORACLE_HOME/network/admin/tnsname.ora에 있는 값 

 

-- db link삭제

 drop  public database link ORACLE1_sms;

 drop            database link ORACLE1_sms;

 

-- db link조회 

 select * from user_db_links;

 select * from all_db_links;

 

 

-- db link를 이용한 자료조회

 SELECT count(*) FROM sms.테이블명@ORACLE1_sms;

 

 

-- db link를 이용한 sp호출

EXECUTE sms.sp_test01@ORACLE1_sms('b');