월드버텍 블로그

–오라클 tnsnames.ora
TESTORCL=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ***.***.***.**)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = test)
    )
  )

–링크드서버구성
exec sp_addlinkedserver
   @server = ‘TESTORCL’
 , @srvproduct = ‘Oracle’
 , @provider = ‘MSDAORA’
 , @datasrc = ‘TESTORCL’

go 

exec sp_addlinkedsrvlogin
  ‘TESTORCL’
 , ‘false’
 , NULL
 , ‘오라클 아이디’
 , ‘오라클 패스워드’
 
–링크드서버 Drop
EXEC sp_droplinkedsrvlogin ‘TESTORCL’, NULL
go
EXEC sp_dropserver ‘TESTORCL’

–생성된 링크드서버 조회
select * from sysservers

select * from sysxlogins

–링크드서버 sql실행
select * from openquery(TESTORCL, ‘select * from 테이블’)

–생성
create public database link vtexlink connect to id identified by password 
  using ‘(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = test)))’;

–조회
select * from tab@vtexlink;