Oracle - db link

[ Oracle - db link ]

 

 

DAGDA@hanafos.com 고성

 

 

1. 원격지에 서버와 로컬 서버간에 조인하려고 할 경우 사용

2. db link 설정 순서

     a. 클라이언트 설정 하기

         - 파일 위치 : C:\app\user\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora

         - 서버의 연결정보 하나 추가

         - cmd에서 : c:\>tngping orcl2  5

             (서버 연결이 잘 되는지 확인)

             (아직 서버 방화벽 설정이 안되어 있기 때문에 연결이 안됨)

    b. 서버 설정 (방화벽 열기)

         - 시작 > [방화벽] -> Windows 방화벽 클릭

         - 왼쪽 목록에서 [고급 설정]

         - 왼쪽 목록에서 [인바운드 규칙]

         - 오른쪽에서 [새규칙]

         - 규칙종류 클릭

             (o) 프로그램    > [다음]

             (o)다음프로그램 경로 [%SystemDrive%\app\user\product\11.2.0\dbhome_1\BIN\oracle.exe]

            (oracle 설치 파일 위치 찾기)

          - [다음] > [다음] > [다음] > 이름[oracle]

    c. 서버 설정 (방화벽 열기)

          - 오른쪽에서 [새규칙]

          - 규칙종류 클릭

             (o) 프로그램    > [다음]

             (o)다음프로그램 경로 [%SystemDrive%\app\user\product\11.2.0\dbhome_1\BIN\LSNRCTL.EXE]

             [다음] > [다음] > [다음] > 이름[lsnrctl]
    d. 서버설정 (port 열기)

           - 규칙종류 클릭

             (o) 포트 > [다음]

             (o) 특정 로컬 포트 : 1521

             name [oracleport]

 

    e. 서버 db restart

 

    f. 클라이언트에서 접속 확인

            - cmd에서 : c:\>tngping orcl2  5

 

    g. 클라이언트에서 서버 db 접속하기

           - cmd창에서 : c:\scott/tiger@orcl2

 

    h. 클라이언트에서 권한 부여

       - 클라이언트에서 서버와 join하기 위해서는 아래처럼 권한을 수행 해 줘야 됨

 

       system> GRANT CREATE PUBLIC DATABASE LINK TO SCOTT;

 

       scott > CREATE PUBLIC DATABASE LINK SEOUL

                  CONNECT TO SCOTT

                  IDENTIFIED BY tiger

                  USING 'db1';

 

    h. 클라이언트에서 원격지와 db 간 join하기

         - SELECT e.ename, d.dname

               FROM emp e, dept@SEOUL d

            WHERE e.deptno = d.deptno;

         

          . 원격지와 join하게 되는 원격지의 table은 항상 TABLE FULL SCAN하게 된다.

            그래서 원격지에서 테이블을 가져오게 되면 속도가 너무 느린 현상이 존재 한다.

            이 해결방법으로는 MATERIALIZED VIEW 를 이용하면 된다.

 

 

3. MATERIALIZED VIEW생성

    system > grant create materialized view to scott;

 

    scott > CREATE MATERIALIZED VIEW dept_sal

                AS

                SELECT a.deptno, SUM(a.sal) sum_sal

                 FROM emp a, dept@SEOUL b

                 WHERE a.deptno = b.deptno

                 GROUP BY a.deptno;

 

    이와 같이 했을 경우에는

        SELECT e.ename, d.deptno, d.sum_sal
           FROM emp e, dept_sal d     --local  db와  join하게 됨
         WHERE e.deptno = d.deptno;

 

    문제점 : 원격지에 자료가 변경시 적용되지 않는다.

 

    MATERIALIZED VIEW 갱신 방법

        - 원격지에서 자료가 변경되었을 경우 자동으로 MATERIALIZED VIEW 자료가 변경되지 않는다.

        - 옵션으로 변경시 언제 갱신할지를 지정할 수 있다.

        - 생성시 OPTION 추가 

            REFRESH ON COMMIT    --원격지에서 COMMIT하면 바로바로 변경됨

            REFRESH ON DEMOND   --요청(DBMS_MVIEW)이 있을 때 갱신

         - REFRESH ON DEMOND option 적용 할 권한 부여

            SYS>GRANT EXECUTE ON DBMS_MVIEW TO SCOTT;

            SCOTT>EXEC DBMS_MVIEW.REFRESH('dept_sal');

                   . 이때 원격지에 변경된 레코드가 로컬 MATERIALIZED VIEW 에 자료가 적용된다.

 

      최종 생성문장 예제)

         - SCOTT>CREATE MATERIALIZED VIEW EMP_VW

                        REFRESH

                        ON COMMIT

                        AS

                        SELECT * FROM EMP;

 

         - 참고 : EMP 테이블에 PK가 존재 하지 않는다면

                     'ORA-12054: 구체화된 뷰에 ON COMMIT 재생 속성을 설정할 수 없습니다'

                     라는 오류가 발생한다.

                     이유는 원본테이블에 자료가 변경 시 MATERIALIZED VIEW 간에 갱신되는 조건을

                     찾아가야 되는데 찾을 수 가 없기 때문이다.

 

                     원본테이블인 EMP 에  PK를 설정하면 'ORA-12054' 오류는 해결 할 수 있다.