매뉴얼

CREATE DATABASE LINK

##CREATE DATABSE LINK

다른 Database에 접근하기 위한 기술입니다.

DB LINK는 Schema Object중 하나입니다.

Schema란, DB에서 Object(객체 지향의 Object가 아님) 네임스페이스를 가르는 역할을 합니다.
Object는 DB 안에 실제하는 객체들을 의미하며, 이는 곧 SQL 명령을 제외한 거의 모든 객체를 뜻합니다.
Schema란, Schema Object들의 집합입니다.
Oracle에서는 Database와 User를 기반으로 Schema를 나누게 되고, 이는 곧 User마다 다른 Schema를 가지도록 합니다.

DB LINK는, 다른 Schema의 Object에 접근할 수 있도록 합니다. 이는 곧, 다른 Machine / User의 테이블에 접근하여 쿼리를 작성할 수 있도록 할 수 있습니다.

문법

하나씩 살펴보면,

• CREATE: CREATE라는 구로 시작합니다.

• [PUBLIC / SHARED]: DB LINK의 사용 범위 / 권한을 지정할 수 있습니다.

  • PUBLIC: 모든 사용자가 동시에 사용 가능한 공용으로 생성.
  • SHARED: 모든 사용자중 1명씩만 사용 가능하도록 생성.
  • PRIVATE: PUBLIC / SHARED 구를 모두 사용하지 않았을 경우, Default로 사용되며 자신만 사용할 수 있도록 생성.

• DATABASE LINK dblink: 생성할 DB LINK의 이름을 정합니다.

  • dblink: 생성할 dblink의 이름.
    • 이름만 정할 경우, local DB의 domain을 붙입니다.
    • ASCII 문자만 사용할 수 있습니다.
    • 사용되고 있는 Schema 이름을 사용하면, 다른 Schema로 인식될 수 있으므로 사용할 수 없습니다.

• CONNECT TO: 연결할 다른 remote DB의 연결 정보를 정합니다.

  • CURRENT_USER: 현재 유저로 연결을 시도합니다.
    • 현재 유저가 해당 remote DB에 대한 접근 권한이 있을 경우만 가능합니다(Global User).
  • user IDENTIFIED BY password: user / passowrd를 이용해서 접근합니다.
    • 고정된 인증 정보를 제공합니다.
    • 이 옵션이 생략되면, DB LINK를 사용하는 각 유저들의 정보가 인증에 활용됩니다.
  • dblink_authentication

• dblink_authentication

• USING connect_string: 원격 DB의 서비스 이름을 지정합니다.

  • 서비스 이름이 지정되지 않으면, Oracle은 현재 domain에 있다고 가정합니다.

• dblink_authentication

  SHARED 구를 사용했을 때만 사용됩니다.

  • AUTHENTICATED BY user IDENTIFIED BY password: user / password를 사용해 인증합니다.
    

remote DB
여기서 말하는 REMOTE_DB란, 다른 service name을 가지는 instance를 뜻합니다.
다른 host일 수도 있고, 아닐 수도 있습니다.

예제

local, remote라는 데이터베이스 2개가 있다고 가정합니다.
사용자가 local, user는 hr로 Global User로 가정합니다(local, remote 모두 권한이 있음).

• 범용

  -- 생성
  CREATE PUBLIC DATABASE LINK remote 
  USING 'remote'; 
  
  -- 사용
  UPDATE employees@remote
  SET salary=salary*1.1
  WHERE last_name = 'Baer';

  'remote'라는 DB에 remote라는 범용 DB LINK를 생성합니다.
  hr는 remote에 대한 권한이 있는 계정일때, 위와 같이 사용 가능합니다.
  

• 고정된 user

  -- 생성
  CREATE DATABASE LINK local 
  CONNECT TO hr IDENTIFIED BY hr
  USING 'local';
  
  -- 사용
  SELECT * FROM employees@local;

  'local'이라는 DB에 hr 유저를 사용해서 local이라는 DB LINK를 생성합니다.
  hr이 생성한 쿼리는, hr만 사용할 수 있습니다.
  

• 현재 user

  -- 생성 1
  CREATE DATABASE LINK remote.us.oracle.com
  CONNECT TO CURRENT_USER
  USING 'remote';
  
  -- 생성 2
  CREATE SYNONYM emp_table 
  FOR oe.employees@remote.us.oracle.com;

  • 생성1: 'remote'라는 DB에 현재 유저(hr)을 사용해서 DB LINK를 생성합니다.
  • 생성 2: 위와 같이 사용하면, 특정 table에 대한 Synonym을 생성하여 다른 테이블 유무 / 사용을 제한할 수 있습니다.

    hr이 oe.employees에 대한 접근 권한이 있다고 가정합니다.

CREATE Synonym

Synonym은 Object에 대한 별명입니다.

Synonym을 사용하면, DB LINK를 더욱 쉽게 사용할 수 있습니다.

주의할 점은, Synonym을 생성한다고 해서, 인증 절차를 무시할 수 있는게 아니라는 것입니다. Synonym을 사용하기 전에 이미 사용할 user가 적절한 권한을 가지고 있어야 합니다.

문법

하나씩 살펴보면,

• CREATE: CREATE 라는 구로 시작합니다.
  • public이어도, 적절한 권한을 가진 user만 사용할 수 있습니다.
• OR REPLACE: 이미 생성된 Synonym을 대체할 것인지를 정해줄 수 있는 구입니다.
• PUBLIC: 이 구를 지정하게 되면, 범용 Synonym이 생성됩니다.
  • user가 사용하는 Schema에 동일한 이름의 Object가 있으면 사용시 오류가 납니다.
• SYNONYM: Synonym을 생성한다는 의미의 구입니다.
• schema .: Synonym을 생성할 Schema를 지정합니다.
  • PUBLIC 구가 있어야 사용할 수 있습니다.
• synonym: 생성할 Synonym의 이름을 지정해줍니다.
• FOR: 생성될 Synonym의 Type을 지정합니다.

  지정할 수 있는 타입들

  Table or object table
  View or object view
  Sequence
  Stored procedure, function, or package
  Materialized view
  Java class schema object
  User-defined object type
  Synonym

  • 생성할 Schema가 존재하지 않거나, 권한이 없어도 생성이 가능합니다(사용시 오류).

• schema .: 해당 객체의 Schema를 지정합니다.
  • 없으면, 현재 user의 Schema에 있다고 가정합니다.
• object: 생성될 Synonym의 타겟 Object.
• @ dblink: 생성할 DB LINK를 지정할 수 있습니다.
  • @ dblink 구를 지정하고, 앞의 schema 구를 지정하지 않으면 Oracle은 @ dblink 구에 Schema 정보가 있다고 판단합니다.

예제

• PRIVATE

  CREATE SYNONYM offices 
  FOR hr.locations;

  hr user(Schema)의 locations 테이블에 대한 Synonym을 생성합니다. hr만 사용 가능합니다.
  
• PUBLIC

  CREATE PUBLIC SYNONYM emp_table 
  FOR hr.employees@remote.us.oracle.com;

  remote DB에 있는 hr user(Schema)의 employees에 대한 Synonym을 생성합니다. remote DB hr.employees에 대한 접근 권한이 있는 user는 모두 사용할 수 있습니다.
  
• 충돌 상황

  oe, sh 유저가 모두 custormers라는 Object를 가지고 있고,hr은 customers라는 Object가 없다고 가정합니다.

  -- PUBLIC으로 생성
  CREATE PUBLIC SYNONYM customers FOR oe.customers;
  
  -- sh가 실행 1
  SELECT COUNT(*) FROM customers;
  
  -- sh가 실행 2
  SELECT COUNT(*) FROM oe.customers;
  
  -- hr이 실행
  SELECT COUNT(*) FROM customers;

  • customers라는 PUBLIC Synonym이 oe.customers를 타겟으로 생성됩니다.
  • sh 실행 1: sh는 customers라는 Object(테이블)을 이미 가지고 있으므로, PUBLIC Synonym보다 자신의 Schema가 우선시 되어 자신의 Object(테이블)을 참조합니다.
  • sh 실행 2: sh가 oe.customers에 접근하려면 Schema까지 모두 써줘야 합니다.

    Synonym의 이름이 달랐다면, customers라는 Object가 없었다면 사용 가능.

  • hr이 실행: hr은 자신의 Schema에 customers라는 Object가 없으므로 oe.customers를 참조하여 사용 가능.

참고

• Oracle Docs CREATE DATABASE LINK
• Oracle Docs CREATE Synonym