농부지기

[ Oracle - DB Scheduler_스케쥴러 ]

오라클 Scheduler

0. 왜 사용했을까?

WEB 프로젝트 수행 도중 LINK된 TABLE의 Data를 1분간격으로 가져와서 WEB Server 내 DB의

Summary Table에 Insert 또는 Update 처리를 하는프로그램이 필요하게 되었다.

먼저 PL/SQL로 프로시져를 작성하고 이를 호출하여 사용하는데 대개의 처리방식은 다음과 같다.

- C프로그램: OCI (로켓프로그램에 사용된다나 뭐라나.. 암튼 일반적으로는 거의 사용 안함?)

- C프로그램: Pro*C (가장 일반적으로 사용하는 방법)

- 자바: JDBC사용

- PL/SQL서버페이지

- 내장패키지 DBMS_JOB을 이용 (Oracle DB가 주기적 실행)

위 처리방법 중 DBMS_JOB을 알아본 결과 사용법도 쉽고 C프로그램 안해도 되고 성능도 좋은것 같아 사용하기로 결정!

1. DBMS_JOB 이란

DBMS_JOB이란 쉽게 말해 오라클에서 주기적으로 수행되는 Backup작업이나, 쿼리나 Stored Procedure등의

JOB들을을 시간단위나 일단위나 월단위 등 주기적인 예약 작업으로 등록하여 동작할 수 있도록 하는 스케쥴러라고

보시면 됩니다. UNIX에서 cron에 등록하여 사용하는 것과 유사한데 차이점은 cron job은 OS가 직접 관리하고

실행하지만 DBMS_JOB에 등록된 JOB은 오라클이 관리를 한다는 것입니다.

2.DBMS_JOB PACKAGE의 사용 방법과 예제

DBMS_JOB package를 이용하여 수행시킬 수 있는데, 이것을 위해서는 SNP background process가 start되어

있어야 합니다.

다음의 parameter를 init.ora file에 설정한 후 oracle을 startup하면 SNP process가 올라옵니다.

job_queue_processes = 1

-> 이 파라미터는 snp process를 몇 개 띄울지를 결정한다.

default=0

job_queue_interval = 60

-> 이 파라미터는 snp process가 깨어나는 간격을 초로 설정한다.

DBMS_JOB Package는 다음과 같은 procedure를 이용하여 사용한다.

DBMS_JOB.submit(job          out binary_integer,

                                 what         in  varchar2,

                                 next_date in  date         defalut   sysdate,

                                 interval     in  varchar2  default   'null',

                                 no_parse in  boolean   default   false )

-> dbms_job.submit  procedure는 job의 내용을 정의하고 oracle이 job을 수행할 수 있도록 한다.

    다음의 예제를 통하여 실제 사용법을 알아보자.

[ 예제 ] file jobcre.sql

begin

dbms_job.submit(:jobno,      -- job 의 번호

                            'insert into scott.testdate values(1, sysdate);',    

                                            -- job의 내용 : ' '으로 감싸준다.

                                            -- procedure를 실행하는 경우 ' username.procedure_name;' 만 쓰면 된다.

                            sysdate,   -- job이 실행될 시간

                            'sysdate + 5/24/60' ,

                                            -- job이 실행되는 간격 , 위의 경우는 5분마다 실행하도록 했다.

                                            -- ' '으로 감싸준다.

                            FALSE );

end;

/

$ sqlplus scott/tiger

SQL> variable jobno number;

SQL> @jobcre

SQL> print jobno -- job 번호 확인 : 여기서는 166번

SQL> exec dbms_job.run(166);

SQL> commit;

지금부터 interval에 따라 job이 실행된다.

스케쥴러 가능내역 확인 테이블

SQL> col what format a20

SQL> select what, job, next_date, next_sec, failures, broken

           from user_jobs;

그 외에

SQL> exec dbms_job.run(jobno);

- job의 강제 실행, job이 16번 fail되어 broken된 경우는

위의 명령어로 강제로 run을 시켜서 실행되면 다시 interval마다

실행된다.

 SQL> exec dbms_job.broken(jobno, TRUE);

- job을 disable시킴

 SQL> exec dbms_job.remove(jobno);

- job의 삭제

 snapshot과 job과의 관계

======================

snapshot 도 job 으로 등록되어 돌아갑니다.

즉, select job, what from dba_jobs; 를 조회하면,

what 부분에 snapshot 이 정의되어 있습니다.

따라서, snapshot 에 대한 disable 방법 등은 job 과 같습니다.

interval 시간 지정 예제

======================

1. 10분에 한번씩 실행하는 경우

    sysdate + 1/24/6 또는 sysdate + 1/144

    -> 1/24 (1시간-60분) / 6 : 10분 단위

    1/144 : 24*6 으로 나누어도 같은 의미가 된다.

2. 1분에 한번으로 지정하는 경우

    sysdate + 1/24/60 또는 sysdate + 1/1440

3. 매일 새벽 2시로 지정하는 경우

    trunc(sysdate) + 1 + 2/24 -> 다음날 새벽 2시를 지정함.

4. 매일 밤 11시로 지정하는 경우

    trunc(sysdate) + 23/24 -> 오늘 밤 11시를 지정했음.

[ Oracle - DB Link ]

[ Oracle - DATABASE LINK 사용 방법 ]

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[at]HP7TOSUN7;

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

       SQL> create synonym emp for emp[at]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[at]HP7TOSUN7;
   insert into table_name[at]HP7TOSUN7;
   delete table_name[at]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[at]link_name;
    synonym : create synonym synonym_name for user_name.table_name[at]link_name;

[ Oracle - DATABASE LINK 사용 방법 ]

먼저 한글 data간의 전달을 위해 두 db간의 character set이 같아야 합니다.

character set을 확인하는 방법은

sqlplus system/manager

select * from v$nls_parameters;

하여 보면 nls_characterset에 대한 값이 나옵니다.

    예를 위해 다음과 같은 환경을 가정하겠습니다.

                      HOST NAME : HP7 - SUN7

                      ORACLE_SID : ORA7 - ORATEST

    이라 할 때

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

            scott/tiger 로 Login

        SQL*NET V1의 경우

            SQL> create public database link HP7TOSUN6

                                connect to scott identified by tiger

                                using 't:SUN7:ORATEST';

        (rdbms 7.3 이상은 SQL*NET v2 를 사용하십시오)

        SQL*NET V2의 경우

            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)을 하려면,

    sql*plus 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 가 반드시 떠 있어야 합니다.)

[ Oracle - DataLink_예제1 ]

1.예제

initXXXX.ora 에서  global_names = false   // false 로 설정되어 있지 않으면 연결되지 않는다... 설정후 오라클을 다시 올린다...

create public database link choi_link

connect to sepsDb identified by seps02

using 'seps'

select * from tb_lca_allo_mst@choi_link

설명....

choi_link : link할 이름

sepsDb : trans.ora 에서 설정해주 이름

seps02 : password

seps   : id

참고 사이트 : http://www.koug.net/

[ Oracle - DB Link 생성 방법 및 SYNONYM 생성 방법 ]

☞ 정의 : 2개의 USER간에 Table을 select/insert/update/delete를 할 수 있도록 Oracle에서 재공하는 Object이다.

[ Oracle - Table  Foreign Key Stop/Start  ]

[ Oracle - Table  foreign key ]

[ Oracle - Tablespace ]

[Oracle  TABLE SIZE 및 INDEX SIZE(크기) 계산]

1. TABLE SIZE 계산 공식(ORACLE BLOCK SIZE : 2K 로 가정)

------------------------------------------------------

   $ sqlplus scott/tiger

     SQL> SELECT   GREATEST(4, ceil(ROW_COUNT /

                 ((round(((1958 - (initrans * 23)) *

                 ((100 - PCTFREE) /100)) / ADJ_ROW_SIZE)))) * BLOCK_SIZE)

                 TableSize_Kbytes

        FROM dual;

   *. 한 개의 BLOCK에 Available 한 Bytes - 1958

   *. 각 initrans 는 23 Bytes

   *. PCT_FREE : Table 의 pctfree 값(default 10)

   *. ADJ_ROW_SIZE : 각 row 의 평균 SIZE 추정치

   *. ROW_COUNT : table 의 row 의 갯수

   *. BLOCK_SIZE : 1 block의 크기 (단위: K)

예) table 이름이 EMP 일 경우

ROW_COUNT : select count(*) from emp;

ADJ_ROW_SIZE :

 analyze table emp compute statistics;

 (또는 건수가 매우 많을 때에는 compute 대신 estimate 사용)

 select avg_row_len

 from user_tables

 where table_name='EMP';

2. INDEX SIZE 계산 공식

-----------------------

   SQL> SELECT   GREATEST(4, (1.01) * ((ROW_COUNT /

                 ((floor(((2048 - 113 - (initrans * 23)) *

                 (1 - (PCTFREE/100))) /

                 ((10 + uniqueness) + number_col_index +

                 (total_col_length)))))) * DB_BLOCK_SIZE))

                 IndexSize_Kbytes

        FROM dual;

   *. 한 개의 block에 available 한 bytes ( 1935 or 2048 - 113 )

   *. 각 initrans 는 23 Bytes

   *. ROW_COUNT : table 의 row 의 갯수

   *. PCTFREE : Index 의 pctfree 값(default 10)

   *. number_col_index : Index 에서 column 의 수

   *. total_col_length : Index 의 길이 추정치

   *. uniqueness : 만일 unique index 이면 1, non-unique index 이면 0.

   *. DB_BLOCK_SIZE : 1 block의 크기 (단위: K)