DBMS_JOB & DBMS_SCHEDULER

DBMS_JOB & DBMS_SCHEDULER

 = 정해진 시간에 반복적으로 작업을 수행할수 있게 해주는 기능

1. DBMS_JOB

- SNP 백그라운드 프로세스가 이 패키지에 설정되어 있는 작업을 수행

패키지

• job_queue_process = 1 : SNP 프로세스의 개수를 지정
• job_queue_interval = 60 : SNP 프로세스가 Sleep 상태에서 깨어나는 간격을 초로 지정

패키지에 있는 프로시저

• submit : 새로운 작업을 job quere 목록에 등록
• remove : job queue 에 등록된 job 을 제거
• change : job queue 에 등록된 job 을 변경
• next_date : job queue 에 등록된 job 의 작동시간을 변경
• interval : job queue 에 등록된 job 의 수행 주기를 변경
• what : 수행할 procedure or package 변경
• run : 등록되어 있는 특정 job 을 수동으로 수행

1) job 관리하기

- 기본 문법

DBMS_JOB.submit (

job out binary_integer,

what in varchar2,

next_date in date default sysdate,

interval in varchar2 default 'null' ,

no_parse in boolean default false )

주요 파라미터 의미    

• job : job 번호로 다른 프로시저에서 호출될 수 있음
• what : 수행할 pl/sql or procedure or package 이름을 지정, 직접 수행하기를 원하는 sql문 적어도 됨
• next_date : 다음에 수행될 시간을 지정
• interval : 수행되는 주기를 지정, 초단위까지 지정가능
• no_parse : parse 여부를 지정, 기본값은 false 로 parse를 수행

실습 1. 새로운 job 등록 테스트하기

job_test01 이라는 테이블을 만든 후 1분에 1번씩 해당 테이블에 데이터를 insert 하는 작업

step 1. 먼저 테스트용 테이블과 sequence , procedure 생성

SCOTT> create sequence seq_job_seq1 ;

Sequence created

SCOTT> create table job_test01

    2      ( no number ,

    3        name varchar2(5)) ;

Table created

SCOTT> create or replace procedure insert_job_test01

    2       is

    3        begin

    4          insert into scott.job_test01

    5          values(seq_job_seq1.nextval , dbms_random.string('a',3)) ;

    6         end ;

    7         /

Procedure created

step 2. job을 등록할 프로시저를 생성

SCOTT> !vi job1.sql

1    begin  

2  dbms_job.submit (:jno ,

3  'scott.insert_job_test01;',

4  sysdate ,      

5  'sysdate +1/24/60' ,       <--1 분에 한번씩 실행

6  false) ;      

7  end;      

8 /

※ 위 5행의 시간 설정 방법 

- 10분에 한번씩 실행하도록 지정

sysdate +1/24/6    or    sysdate +1/144

      일/시간/분 

1440 / 24 / 6    or 1440 / 144   분단위로 계산

- 1분에 한번씩 지정

sysdate +1/24/60    or    sysdate +1/1440

- 5분에 한번씩 지정

sysdate +5/24/60  

- 매일 새벽 2시로 지정

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

( trunc 함수를 지정하는 이유는 분:초 단위까지 나오지 않게 하기 위해서 사용 )

step 3. job 등록

SCOTT> variable jno number ;

SCOTT> @job1.sql

PL/SQL procedure sucessfully completed.

SCOTT/print jno ;

JNO

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

25                 <-- 현재 할당된 JOB 번호 확인, 이 번호는 random으로 지정됨

SCOTT> commit ;     <-- 이 순간부터 job이 수행됨, 즉 commit 이 안되면 job 수행 안됨

RAC 환경에서 특정 노드에 job 을 할당해야 할 경우

SCOTT> exec dbms_job.instance(job=>25, instance=>1) ; 

step 4. 수행되고 있는 job 내역 확인

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

2     from user_jobs

3     where what = 'scott.insert_job_test01 ;' ;

step 5. 실제 테이블에 데이터가 입력되는지 확인

SCOTT> SELECT * FROM job_test01 ;

실습을 제대로 따라 왔으면 데이터가 1분에 하나씩 늘어나는것을 확인 할 수 있음

실습 2. 등록되어 있는 job 삭제

SCOTT> exec dbms_job.remove(25) ;      <-- 숫자부분에는 job 번호를 기입

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

2    from user_jobs

3    where job=25 ;

-> 사라져서 안나옴

실습 3. 등록되어 있는 job 수정

테스트를 위해 다시 job 등록

SCOTT> variable jno number ;

SCOTT> @job1.sql

SCOTT> print jno ;

JNO

-----------

26               <-- job no 가 26으로 변경되있음

SCOTT> commit ;

SCOTT> select what, job, next_date, next_sec, interval

2    from user_jobs

3    where job=26 ;

WHAT                JOB    NEXT_DATE            NEXT_SEC             INTERVAL

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

scott.insert_job     4       15-OCT-14            21:51:30             sysdate +1/24/60

수행시간을 1분마다 -> 5분마다로 변경

SCOTT> exec dbms_job.change(:jno, 'scott.insert_job_test01;', sysdate, 'sysdate +5/24/60') ;

SCOTT> select what, job, next_date, next_sec, interval

2    from user_jobs

3    where job=26 ;

WHAT                JOB    NEXT_DATE            NEXT_SEC             INTERVAL

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

scott.insert_job     4       15-OCT-14            21:51:30             sysdate +5/24/60

2. DBMS_SCHEDULER ( 10g이상에서만 사용가능 )

dbms_job 패키지보다 확장된 기능으로 dbms_job 에서 안되는 외부 스크립트를 실행 할 수 있음 

(1) 주요특징

- 오라클에서 생성된 프로시저나 함수 외에 os에서 생성된 각종 유틸이나 프로그램까지도 실행가능

- 설정할 때 일반적으로 사용하는 자연어를 사용하여 편하게 설정 가능

- db 내부이벤트까지 추적가능하기 때문에 os나 dbms_job 보다 훨씬 다양하게 작업을 체크하고 수행가능

(2) 구성

- program : 이 부분에 dbms_scheduler가 수행할 프로그램이나 프로시저, 스크립트 등에 대한 정보를 저장해서 별도로 생성할 수 있음

- schedule : 이 부분에 dbms_scheduler 가 수행할 job 이 실제 수행할 시간이나 실행 주기등을 별도로 생성 할 수 있음

- job : 새로 생성할 job 부분을 정의

(3) dbms_scheduler 사용하기

실습 1. 신규 job 생성하기

job_tetst1 이라는 테이블에 3초에 1회씩 데이터를 insert 하는 작업을 설정

이 작업을 하기 위해선 해당 계정이 create any job 권한을 가지고 있어야 함

scott 계정에 권한 부여

SYS> grant create any job to scott ;

SYS> conn scott/tiger ;

SCOTT> create table job_test1

2    ( no number ,

3     name varchar2(5) ,

4     rdate date default sysdate ) ;

SCOTT> create sequence seq_job_test1_no ;

SCOTT> create or replace procedure insert_job_test

2    is

3     begin

4      insert into scott.job_test1 (no , name)

5      values(seq_job_test1_no.nextval, dbms_random.string('a',2)) ;

6     commit ;

7    end;

8    /

SCOTT> begin

  2  dbms_scheduler.create_job(                                   <-- 신규 JOB을 생성

  3     job_name => 'insert_job_test1' ,                           <-- dbms_scheduler 내에서 사용될 job 이름지정

  4     job_type => 'plsql_block' ,                                    <-- 5번줄에 적은 프로그램의 타입을 적음

  5     job_action => 'begin insert_job_test; end;' ,            <-- 실제 실행될 프로그램을 적는 부분

  6     start_date => systimestamp ,                                <-- 해당 job 이 처음 시작될 시간을 지정

  7     repeat_interval => 'freq=secondly; interval=3' );        <-- 반복할 주기를 지정

  8  end;

  9  /

※ 5번줄에는 1) 실제 PL/SQL 블록  

 2) 프로시저 이름

 3) OS에 있는 실행파일

 4) program_name 으로 미리생성해둔 프로그램 이름

 5) chained 값으로 생성된 체인

위 5가지가 들어갈 수 있음 

※ 4번줄은 위에 pl/sql 형태이므로 PLSQL_BLOCK 로 기입

5번줄이 프로시저 이름을 적었다면 4번줄에는 STORED_PROCEDURE로,

실행프로그램을 적었다면 EXECUTABLE 로 적어야 함 

※ 7줄의 주기 설정

repeat_interval => 'freq=hourly ; interval=1'            <- 1시간 간격으로 수행하도록 설정

repeat_interval => 'freq=minutely ; interval=30'       <- 30분 간격으로 수행하도록 설정

repeat_interval => 'freq=secondly ; interval=5'        <- 5초 간격으로 수행하도록 설정     

repeat_interval => 'freq=weekly ; interval=2'           <- 2주 간격으로 수행

repeat_interval => 'freq=hourly ; interval=1'            <- 매달 수행하도록 설정

repeat_interval => 'freq=hourly ; interval=1'            <- 매년 수행하도록 설정

SCOTT> exec dbms_scheduler.enable('insert_job_test1') ;

SCOTT> exec dbms_scheduler.run_job('insert_job_test1') ;

SCOTT> alter session set nls_date_format='YYYY-MM-DD:HH24:MI:SS' ;

SCOTT> select * from job_test1 order by 3 ;

-> 확인해보면 3초마다 insert 되는것을 볼 수 있음

실습 2. 현재 작동중인 job 확인

 SYS> select p.job_name, p.job_type, o.object_id, p.enabled, o.last_ddl_time, o.created,

  2         cast(p.next_run_date as date) next_run_date, p.state, p.job_class, o.owner,

  3         schedule_type

  4  from dba_objects o , dba_scheduler_jobs p

  5  where o.owner=p.owner

  6  and o.object_name=p.job_name

  7  and o.object_type='JOB'

  8  and o.owner='SCOTT'

실습 3. 특정 job을 일시중지 또는 재시작 하기

SCOTT> exec dbms_scheduler.disable ('insert_job_test1') ;        <-- 일시중지

SCOTT> exec dbms_scheduler.enable ('insert_job_test1') ;         <-- 다시시작

실습 4. 특정 job 을 삭제

SCOTT> begin

2    dbms_scheduler.drrop_job('insert_job_test1') ;

3    end ;

4    /

실습 5. 외부 스크립트를 생성해서 실행

외부에서 생성된 스크립트를 오라클에서 실행하기 위해서는 해당 스크립트를 실행할 권한과 그룹이 지정되어 있는 externaljob.ora 라는 파일을 수정해야 함

step1. externaljob.ora 파일의 내용을 수정

SYS> !vi /app/oracle/product/11g/rdbms/admin/externaljob.ora

마지막줄의 내용을 아래와 같이 변경

#run_user = nobody     -- 기존내용 주석처리

#run_group = nobody    -- 기존 내용 주석처리

run_user = oracle

run_group = oinstall

oracle 계정의 소속 그룹을 모를 경우

->$ id oracle 조회

step2. 수정 완료 후 위 파일의 권한을 640 상태로 설정 ( 이 값이 기본적으로 설정되어 있음 )

[oracle@localhost ~] $ls- l  $ORACLE_HOME /rdbms/admin/externaljob.ora

step3. 외부 스크립트를 실제로 수행하는 명령인 extjob 파일의 소유자를 변경

extjob 파일의 소유자 root -> oracle 로 변경

[oracle$localhost ~]$ ls -l $ORACLE_HOME/bin/extjob

[root$localhost ~]# chown oracle extjob

[root$localhost ~]# ls -l extjob

이 작업을 하지않을 경우 permission denied 라는 에러가 발생