[Oracle Admin] Chap 11. DBMS_JOB & DBMS_SCHEDULER

Chap 11. DBMS_JOB & DBMS_SCHEDULER

이 두가지는 반복되는 작업을 수행해야 할 경우 아주 유용하다. 

  1. DBMS_JOB 

1) dbms_job 패키지

-  이 패키지를 설정하게 되면 SNP 백그라운드 프로세스가 이 패키지에 설정되어 있는 작업을 수행하게 된다. SNP 백그라운드 프로세스는 선택적인 프로세스로 초기화 파라미터 파일에 아래의 항목을 설정 한 후 재 시작을 하면 실행된다. 

* job_queue_process = 1  : SNP 프로세스의 개수를 지정

* job_queue_interval = 60  : SNP 프로세스가 sleep 상태에서 꺠어나는 간격을 초로 지정. 

- dbms_job 패키지는 아래와 같은 프로시저들을 가지고 있다. 

* submit : 새로운 작업을 job queue 목록에 등록한다. 

* remove : job queue 에 등록된 job 을 제거한다. 

* change : job queue 에 등록된 job 을 변경한다. 

* next_date : job queue 에 등록된 job 의 작동 시간을 변경한다. 

* interval : job queue 에 등록된 job 의 수행 주기를 변경한다. 

* what : 수행할 procedure or package 를 변경한다. 

* run : 등록되어 있는 job 을 수동으로 수행한다. 

2) job 관리하기

- 기본 문법

dbms_job.submit(

job out binary_integer,      ### job number

what in varchar2,             ### 수행할 pl/sql, procedure, package 이름 이나 직접 SQL 문장을 써도 된다.

next_date in date default sysdate,       ### 다음에 수행된 시간 지정

interval in varchar2 default 'null',        ### 수행되는 주기를 지정하며 초 단위까지 지정가능

no_parse in boolean default false       ### parse 여부를 지정한다. 

)

- 새로운 job 등록 테스트 하기

## STEP1 - table, sequence, procedure 생성

SCOTT> create sequence seq_job_seq1;

Sequence created.

SCOTT> create table job_test01 (no number, name varchar2(5) );

Table created.

SCOTT> create or replace procedure insert_job_test01 is

  2  begin

  3  insert into scott.job_test01 values(seq_job_seq1.nextval, dbms_random.string('a',3));

  4  end;

  5  /

 

Procedure created.

## STEP2 - job 을 등록할 procedure 생성

SCOTT> !vi job1.sql

begin

  dbms_job.submit(:jno,

  'scott.insert_job_test01;',

  sysdate,

  'sysdate + 1/24/60',

  false);

end;

 

/

:wq!

==> 여기서 :jno 의 의미는 job number 를 oracle 이 알아서 할당하게 하라는 의미이다. 

## STEP3 -  job 을 등록

SCOTT> variable jno number;    ==> jno 에 number 할당

SCOTT> @job1.sql

 

PL/SQL procedure successfully completed.

SCOTT> print jno;

       JNO

----------

 

 5

SCOTT> commit;      ==> 이 시간 부터 job 이 실행된다. commit 을 안하면 수행 안 됨. 

 

Commit complete.

## STEP4 - 수행되고 있는 job 내역 확인

SCOTT> select what, job, next_date, next_sec, failures, broken from user_jobs where what='scott.insert_job_test01;';

WHAT  JOB NEXT_DATE    NEXT_SEC      FAILURES B

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

scott.insert_job_test01;    5 10-JUL-13    12:21:49       N

SCOTT> select * from job_test01 order by no; 

NO NAME

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

1 PbD

2 TWN

3 mqi

4 Izh

5 yer

6 Kji

 

6 rows selected.

- 등록되어 있는 job 삭제하기

SCOTT> print jno;           

       JNO

----------

 5

SCOTT> 

SCOTT> exec dbms_job.remove(5);

PL/SQL procedure successfully completed.

SCOTT> select what, job, next_date, next_sec, failures, broken from user_jobs where job=5;

no rows selected

- 등록되어 있는 job 수정하기

## test 위해 다시 job 등록

SCOTT> variable jno number;

SCOTT> @job1.sql

PL/SQL procedure successfully completed.

SCOTT> print jno;

       JNO

----------

6

SCOTT> commit; 

 

Commit complete.

SCOTT> select what, job, next_date, next_sec, interval from user_jobs where job=6;

WHAT  JOB NEXT_DATE    NEXT_SEC    INTERVAL

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

 

scott.insert_job_test01;    6 10-JUL-13    12:30:10    sysdate + 1/24/60

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

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

 

PL/SQL procedure successfully completed.

SCOTT> select what, job, next_date, next_sec, interval from user_jobs where job=6;

WHAT  JOB NEXT_DATE    NEXT_SEC    INTERVAL

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

 

scott.insert_job_test01;    6 10-JUL-13    12:32:36    sysdate + 5/24/60

  2. DBMS_SCHEDULER   

1) 주요 특징 및 구성

- dbms_job 이 할 수 없는 일들을 하기 위해 생성.

* oracle 에서 생성한 procedure 나 function 이외에 OS 에서 생성된 각종 유틸이나 프로그램도 실행 가능

* 데이터 베이스에서 내부 이벤트까지 추적가능하기 때문에 OS 나 dbms_job 보다 훨씬 다양하게 작업을 체크/수행가능

2) DBMS_SCHEDULER 사용하기

- 신규 job 생성

일반적으로 dbms_scheduler.create_job 이나 dbms_scheduler.create_program 을 이용할 수 있으나 전자를 더 많이 쓴다.

## job_test1 이라는 table 에 3초에 한번씩 데이터를 insert 해보자. 이 작업을 하기 위해서는 해당 계정이 create any job 의 권한을 가지고 있어야 한다. 

SYS> grant create any job to scott;

Grant succeeded.

SYS> conn scott/tiger

Connected.

## test 할 table, sequence, procedure 생성

SCOTT> create table job_test1 (no number, name varchar2(5), rdate date default sysdate);

Table created.

SCOTT> create sequence seq_job_test1_no;

Sequence created.

SCOTT> create or replace procedure insert_job_test

  2  is

  3  begin

  4  insert into scott.job_test1 (no, name) values (seq_job_test1_no.nextval, dbms_random.string('a',2));

  5  commit;

  6  end;

 

  7  /

Procedure created.

## 신규 job 생성

SCOTT> begin

  2  dbms_scheduler.create_job(  

  3    job_name => 'insert_job_test1',

  4    job_type => 'PLSQL_BLOCK',

  5    job_action => 'BEGIN insert_job_test; END;',

  6    start_date => systimestamp,

  7    repeat_interval => 'freq=secondly; interval=3');

  8  end;

  9  /

PL/SQL procedure successfully completed.

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

각 라인 설명 : 

2 > 신규 job 을 생성하라는 의미 

3 > dbms_scheduler 내에서 사용 될 job 의 이름. 중복되면 안됨.

4 > 5번 줄에 적히는 항목에 따라 값이 달라진다. 이 곳은 5번줄에 적힌 프로그램 타입을 적는 곳이다. 현재는 5번줄에 begin~end 형태의 pl/sql 형태로 적었기 때문에 PLSQL_BLOCK 가 된 것이고, 만약 프로시저 이름이라면, STORED_PROCEDURE 라고 적어야 한다. 또, 실행 프로그램이라면 EXECUTABLE 라고 적어주면 된다. 

5 > 실제 실행 될 프로그램을 적는 부분. 이 곳에서는 실제 pl/sql 블록을 적을 수도 있고, 프로시저 이름을 적을 수도 있고, os에 있는 실행파일 이름을 지정할 수도 있으며, program_name 으로 미리 생성해 둔 프로그램 이름을 지정할 수도 있고, chained 값으로 생성된 체인을 활용할 수도 있다. 

6 > 해당 job 이 처음 시작될 시간을 지정하는 곳이다. systimestamp 는 지금 즉시 시작하라는 의미이며, 특정 시간에 시작하도록 지정하고 싶으면, start_date => to_timestamp_tz('2013-07-01 02:00 ROK', 'YYYY-DD-DD HH24:MI:SS TZR) 이런 식으로 설정하면 된다. 또한 종료 시간을 설정하고 싶으면 end_date => systimestamp + interval '30' day  이런식으로 줄 수 있다. 

7 > 반복할 주기를 지정하는 곳이다. 반복 주기를 지정하는 방법은 아래와 같이 2가지가 있다. 

* 얼마마다 한번씩 수행한다는 형식 (CALENDARING Expression) 

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=monthly'                      ----- 매달 수행하도록 설정

위의 방법에 by 를 사용해서 보다 구체적으로 시간을 지정할 수 있다. 예를 들어, 매주 일요일 밤 20 시 30분에 수해하고 싶다면, 

repeat_interval => 'freq=weekly; byday=sun; byhour=20; byminute=30;'

또 다른 옵션으로는 다른 일정을 현재 일정에 포함할 수 있는 include 가 있다. 

BEGIN

dbms_scheduler.create_schedule (

  schedule_name => 'sub_sched_1',

  repeat_interval => 'freq=yearly; bydate=0101,0201,0301');

dbms_scheduler.create_schedule (

  schedule_name => 'main_sched',

  repeat_interval  => 'freq=monthly;interval=2; bymonthday=15; byhour=9,17; include=sub_sched_1');

END;

/

* 다음 직접 작업 시간까지의 간격을 지정하기 

아래와 같이 설정 할 수 있다. 

repeat_interval => 'SYSDATE + 1'         ----- 하루 뒤에 수행 

repeat_interval => 'SYSDATE + 30/1440'   --- 1440 은 하루를 분으로 환산한 것이니 30분 마다 수행하라는 의미

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

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

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

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

Session altered.

SCOTT> select * from job_test1 order by 3;

NO NAME  RDATE

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

1 Eo 2013-07-10:12:50:14

2 OY 2013-07-10:12:50:17

3 XM 2013-07-10:12:50:20

4 Hu 2013-07-10:12:50:23

5 il 2013-07-10:12:50:26

6 Da 2013-07-10:12:50:29

7 xl 2013-07-10:12:50:29

8 AR 2013-07-10:12:50:32

9 kx 2013-07-10:12:50:35

10 Xc 2013-07-10:12:50:38

11 Qv 2013-07-10:12:50:41

12 qF 2013-07-10:12:50:44

13 EG 2013-07-10:12:50:47

14 lN 2013-07-10:12:50:50

15 jA 2013-07-10:12:50:53

16 zB 2013-07-10:12:50:56

17 he 2013-07-10:12:50:59

18 BM 2013-07-10:12:51:02

19 YF 2013-07-10:12:51:05

 

19 rows selected.

- 현재 작동중인 job 확인하기 

SYS> select p.job_name, p.job_type, o.object_id, p.enabled, o.last_ddl_time, o.created, cast(p.next_run_date as date) next_run_date, p.state, p.job_class, o.owner, schedule_type

  2  from dba_objects o, dba_scheduler_jobs p

  3  where o.owner=p.owner and o.object_type='JOB' and o.owner='SCOTT';

JOB_NAME       JOB_TYPE OBJECT_ID ENABL LAST_DDL_TIM CREATED   NEXT_RUN_DAT STATE JOB_CLASS       OWNER      SCHEDULE_TYP

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

INSERT_JOB_TEST1       PLSQL_BLOCK     75689 TRUE  15-JUL-13    15-JUL-13    15-JUL-13 SCHEDULED DEFAULT_JOB_CLASS       SCOTT      CALENDAR

- 특정 job 을 일시 중지 또는 재 시작 하기

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

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

- 특정 job 을 삭제하기

SCOTT> begin

  2  dbms_scheduler.drop_job('insert_job_test1');

  3  end;

  4  /

PL/SQL procedure successfully completed.

- Job 의 속성 변경하기 

속성변경 테스트를 위해서 job 을 하나 더 생성해서 테스트 해보겠다. 

## 새 작업 만들기

SCOTT> begin

  2  dbms_scheduler.create_job (

  3  job_name => 'insert_job_test2',

  4  job_type => 'PLSQL_BLOCK', 

  5  job_action => 'BEGIN insert_job_test; END;',

  6  start_date => systimestamp,

  7  repeat_interval => 'freq=secondly; interval=5' );

  8  end;

  9  /

 

PL/SQL procedure successfully completed.

==> 7 라인에 보면 5초마다 한번씩 수행되는 것으로 생성이 되어 있다. 이 부분을 10초에 한번씩으로 바꾸어 보겠다. 

SCOTT> exec dbms_scheduler.set_attribute(name => 'insert_job_test2', attribute => 'repeat_interval', value => 'freq=secondly;interval=10' );

PL/SQL procedure successfully completed.

### sys 계정으로 바꼈는지 조회

SYS> select owner, job_name, start_date, repeat_interval from  dba_scheduler_jobs where owner='SCOTT';

OWNER     JOB_NAME    START_DATE REPEAT_INTERVAL

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

SCOTT     INSERT_JOB_TEST2    15-JUL-13 01.55.33.357796 PM +09:00 freq=secondly;interval=10

3) DBMS_SCHEDULER 관리하기

- 생성된 job을 즉시 실행하기 

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

- job 을 중단하기 

SCOTT > exec dbms_scheduler.stop_job(job_name => 'insert_job_test1', force => true);

- job 삭제하기 

SCOTT > exec dbms_scheduler.drop_job(job_name => 'insert_job_test1', force => true);