DBMS/오라클] Database Link사용법

출처: http://bosoa.egloos.com/1442337

Database Link사용법

원격지에 있는 데이터베이스를 link하는 법은 다음과 같다.

원격지의 Database의 Service Name이 piruks.kang.com이다.
나의 tnsnames.ora파일에는 Database alias가 piruks가 잡혀있다.

piruks.kang.com(원 격지DB)의 init.ora내용
db_name = "piruks"
db_domain = kang.com
instance_name = piruks
service_names = piruks.kang.com

maddog.kang.com(로컬 DB)의 tnsnames.ora내용
PIRUKS =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.102)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = PIRUKS.KANG.COM)
    )
  )





원 격지의 패러미터파일(init.ora)에 global_names를 true로 하면 dblink의 이름과 접속하는 db의 이름이 동일하도록 요구한다.

global_names = true [ or false ]

이 설정은 다음과 같이 해서 알 수 있다.
SQL> select name, value from v$parameter where name='global_names';

NAME                 VALUE
-------------------- ------
global_names         FALSE


현재 데이터베이스의 이름은 global_names에 질의함으로써 알 수 있다.

SQL> connect kang/xxxxxx@piruks;
연 결되었습니다.
SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------
PIRUKS.KANG.COM



local db에 kang이라는 사용자로 접속한다.
SQL> connect kang/xxxxxx
연결되었습니다.

이 제 원격지(piruks.kang.com)의 DB에 kang이라는 사용자로 연결되는 Database link
를 생성한다.
여기서 piruks.kang.com은 원격지의 [Database name].[Domain name]이다.
보통 [Database name].[Domain name]는 Service Name이라 불리운다.
하지만 위와 같이 해서 제대로 않되는 경우가 있다.
이럴때는 global_name에 있는 이름을 dblink이름으로 정하면 해결된다.
piruks는 database connect string이다.
SQL> create database link piruks.kang.com
  2  connect to kang identified by xxxxxx
  3  using 'piruks';
데이타베이스 링크가 생성되었습니다.



사 용법은 다음과 같다.
보통의 DML문장에 '@piruks.kang.com'을 추가한다.
SQL> select * from tab at piruks.kang.com;

TNAME                                                        TABTYPE         CLUSTERID
------------------------------------------------------------ ------------
-- ----------
TEST                                                         TABLE

SQL> select * from test at piruks.kang.com;

NAME                        AGE
-------------------- ----------
강명규                       27

SQL> insert into test at piruks.kang.com values('홍길동', 30);

1 개의 행이 만들어졌습니다.

SQL> update test at piruks.kang.com set age=31 where age=30;

1 행이 갱신되었습니다.

SQL> select * from test at piruks.kang.com;

NAME                        AGE
-------------------- ----------
강명규                       27
홍길동                       31

SQL> delete from test at piruks.kang.com where age=31;

1 행이 삭제되었습니다.

데이터베이스링크에서 DDL문장은 적용되지 않는다.
SQL> drop table test at piruks.kang.com;
drop table test at piruks.kang.com
                *
1 행에 오류:
ORA-02021: 원격 데이터베이스에 DDL 조작들이 허용되지 않습니다


SQL> drop database link piruks.kang.com;

데이타베이스 링크가 삭제되었습니다.

SQL>

삭 제할대 DBLINK의 소유자로 삭제 하여야 한다.

========================================================================
Database Link(데이터베이스 링크)
먼저 디비 링크를 사용하기 위해서는 v$parameter 에 global_names=false로 되어 있어
야 합니다..
init.ora를 고치던지.아님 alter system set global_names=false;로 해 줘야 합니다.
그리고 grant create database link to scott; 로 해당 유저한테 권한을 부여 합니다.
그리고 tnsnames.ora파일을 수정해 해당 연결하고자 하는 database의 알리아스를 만
들어 주면 됩니다.

우선 고려되어야 사항은 ORACLE INSTANCE가 두 개 이상이고 각기 다른 PLATFORM에
서 운용된다는 가정하에서 각각의 HOST NAME과 ORACLE_SID는 다르고
NLS_CHARACTER_SET 은 동일하게 되어 있어야 합니다.

만약 같은 MACHINE에서 INSTANCE의 ORACLE_SID가 같다면 TNS ERROR가 발생할
것이다.
또한 미래를 위해 다른 MACHINE이라 할지라도 ORACLE_SID는 규칙에 의해 다르게 가
져가는 것이 좋습니다.
그리고, NLS_CHARACTER_SET이 동일하게 되어 있지 않으면 DATA 입/출력 시 한글 데
이타가 ?????로 나타날 것입니다.

그럼 환경 점검이 끝났으니 ORACLE7에서 ORACLE V6(ORACLE7의 경우도 비슷)에 있
는 TABLE의 DATA를 DB LINK를 이용하여 SELECT하거나 VIEW를 작성하여 보겠습니
다.

HOST NAME    :      HP7    ->    SUN7     
ORACLE_SID   :      ORA7   -     ORATEST  이라 할 때

   1)  HP7 에서 SUN7로 dblink생성하기.

       scott/tiger 로 Login

       SQL> create public database link HP7TOSUN7
            connect to scott identified by tiger
            using 'ORATEST';
       로 하면 된다.

       이때 V2인 경우의 ORATEST는 $ORACLE_HOME/network/admin directory의
       tnsnames.ora file 내에 지정된 service name이다.
       tnsnames.ora의 service name이 잘 setting 되어 있는지 확인하는 방법
       : SQL*Plus scott/tiger@service name했을 때, SQL*Plus에 log-in되어야
       합니다.

   2> SUN7 에 있는 TABLE의 select 및 view(view는 필요에 따라 생성) 작성,
      HP에서 작업
   
       SQL> select * from emp@HP7TOSUN7;

       SQL> create view emp_view as select * from emp@HP7TOSUN7  a
                 where a.deptno = 10;
                   
   3> HP7 에서 SYNONYM을 생성하여 사용하는 경우

       SQL> create synonym emp for emp@HP7TOSUN7;
       SQL> select * from emp;

로 한다면 간단히 분산 DB의 환경에서 사용 할 수 있습니다.

select 를 제외한 DML(insert,update,delete) 를 하려면,sqlplus log-in 시에 다음과 같
은 option 이 display 되어야 합니다.

   SQL*Plus: Release 3.3.3.0.0 - Production on Mon Jan 19 14:18:47 1998
   Copyright (c) Oracle Corporation 1979, 1996.  All rights reserved.

   Connected to:
   Oracle7 Server Release 7.3.3.4.0 with the 64-bit option - Production
   Release With the distributed, ......
   ------------------------------------

   remote 작업의 예

   select * from table_name@HP7TOSUN7;
   insert into table_name@HP7TOSUN7;
   delete table_name@HP7TOSUN7;

   (단 SERVER TO SERVER로 NETWORK 환경이 구축되어 있어야 하고,
   listener 가 반드시 떠 있어야 합니다.)

SQL>   create public database link link_test
  2    connect to scott identified by tiger
  3    using 'haksan21'; -- service name

데이타베이스 링크가 생성되었습니다.

SQL>  select * from all_db_links;

OWNER      DB_LINK              USERNAME   HOST       CREATED
---------- -------------------- ---------- ---------- --------
PUBLIC     HSA.WORLD            HSA        haksan21   99/03/12
PUBLIC     HSE.WORLD            HSE        haksan21   99/03/30
PUBLIC     HSR.WORLD            HSR        haksan21   99/03/23
PUBLIC     LINK_TEST.WORLD      SCOTT      haksan21   99/05/07

SQL> drop public database link link_test;

데이타베이스 링크가 삭제되었습니다.

SQL> select * from all_db_links;

OWNER      DB_LINK              USERNAME   HOST       CREATED
---------- -------------------- ---------- ---------- --------
PUBLIC     HSA.WORLD            HSA        haksan21   99/03/12
PUBLIC     HSE.WORLD            HSE        haksan21   99/03/30
PUBLIC     HSR.WORLD            HSR        haksan21   99/03/23

** export, import 작업을 수행할 경우 (또는 동일한 환경으로 새로운 유저를 만들경
우) ??

Synonym 처음
* 목적 : 다른 사용자가 소유한 테이블을 쉽게 사용하기 위해
         OBject 이름을 짧게 하기 위해

* SYNONYM의 정보를 가진 Data Dictionary
  USER_SYNONYMS
  ALL_SYNONYMS

* 예문
  create synonym tmp00 for fsr.tmp00;

* 삭제 : drop synonym synonym_name

*** DB LINK의 사용

    다른 기계의 데이타베이스를 사용하고자 할 때 이용
   
    create public database link link_name
    connect to user_name identified by password
    using 'db_alias_name';
   
    확인 : all_db_links
   
    사용 : select * from user_name.table_name@link_name;
    synonym : create synonym synonym_name for
user_name.table_name@link_name;

View 처음
* 뷰는 테이블이나 다른 뷰를 기반으로 한 논리적 테이블이다. 스스로 자료를 가지지 않으나 조회, 수정할 수 있는 창문과 같다. 뷰의 근거가 되는 테이블을 base table이라 한다. 뷰 는
  select 문장의 형태로 data dictionary에 저장된다.

* 잇점 : 1) DB의 특정 부분만 조회하므로 접근을 제한할 수 있다.
         2) 복잡한 질의를 단순화할 수 있다. (여러 테이블의 조인관계를 숨길 수 있다.)

* 뷰를 만드는 여러 예문
  create or replace view empvu30
  as select * from emp where deptno = 30
     with check option constraint empvu30_ck; -- deptno가 30인 자료만 가능

  create or replace view empvu30 (id_number, employee, job) -- alias 변경
  as select empno, ename, job from emp where deptno = 30 with read only; --
delete불가
 
  create or replace view v_yycnt as
  select sum(aa) aa, sum(bb) bb, sum(cc) cc
  from (
       select count(*) aa, 0 bb, 0 cc from emp
        where to_char(hiredate, 'yy') = '81'
       union all
       select 0 aa, count(*) bb, 0 cc from emp
        where to_char(hiredate, 'yy') = '82'
       union all
       select 0 aa, 0 bb, count(*) cc from emp
        where to_char(hiredate, 'yy') = '83');
 
* 뷰의 정보를 가진 Data Dictionary
  USER_VIEWS;
  ALL_VIEWS;
  DESC view_name;

* 삭제 : drop view view_name;