1. DBMS_JOB 패키지 살펴보기
job_queue_process = 1 SNP 프로세스의 개수를 지정
job_queue_interval = 60 SNP 프로세스가 sleep 상태에서 깨어나는 간격을 초로 지정
submit 새로운 작업을 job queue 목록에 등록
remove job queue 에 등록된 job 을 제거
change job queue 에 등록된 job 을 변경
next_date 작동 시간 변경
interval 수행주기 변경
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 등록 테스트 하기
step1 테스트용 테이블과 sequence, procedure 생성
SYS>conn scott/tiger
Connected.
SCOTT>
SCOTT>
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 /
step2 job 을 등록할 프로시저를 생성한다
SCOTT>!vi job1.sql
begin
dbms_job.submit (:jno,
'scott.insert_job_test01;',
sysdate,
'sysdate + 1/24/60', -- 1분에 한번씩 실행
false);
end;
/
시간 설정 방법
10분에 한번씩 실행
sysdate + 1/24/6 또는 sysdate + 1/144
1/24 (1시간 - 60분) / 6 : 10분 단위, 1/144 : 24*6 으로 나누어도 같은 의미
(1일은 1440 분이므로)
1분에 한번으로 지정하는 경우
sysdate + 1/24/60 또는 sysdate + 1/1440
5분에 한번
sysdate + 5/24/60
매일 새벽 2시
trunc(sysdate) + 1 + 2/24 다음날 새벽 2시를 지정
trunc 함수를 사용하지 않을경우 분:초단위까지 나오게 된다
매일 밤 11시
trunc(sysdate) + 23/24 오늘 밤 11시
step3 job을 등록
SCOTT>variable jno number ;
SCOTT>
SCOTT>@job1.sql
PL/SQL procedure successfully completed.
SCOTT>print jno ;
JNO
----------
4
SCOTT>commit ;
Commit complete.
step4. 수행되고 있는 job 내역을 확인한다
1 select what, job, next_date, next_sec, failures, broken
2 from user_jobs
3* where what = 'scott.insert_job_test01;'
step5 실제 테이블에 데이터가 입력되는지 확인한다.
SCOTT>select * from job_test01 ;
(2) 등록되어 있는 job 삭제하기
SCOTT>exec dbms_job.remove(3) ;
PL/SQL procedure successfully completed.
SCOTT>exec dbms_job.remove(4) ;
PL/SQL procedure successfully completed.
SCOTT>select what, job, next_date, next_sec, failures, broken
2 from user_jobs
3 where job = 25 ;
(3) 등록되어 있는 job 수정하기
SCOTT>variable jno number ;
SCOTT>@job1.sql
PL/SQL procedure successfully completed.
SCOTT>print jno ;
JNO
----------
5
SCOTT>
SCOTT>
SCOTT>commit ;
Commit complete.
SCOTT> select what, job, next_date, next_sec, interval
2 from user_jobs
3* where job = 5
SCOTT>exec dbms_job.change(:jno, 'scott.insert_job_test01;', sysdate, 'sysdate + 5/24/60') ;
PL/SQL procedure successfully completed.
SCOTT>set line 200
SCOTT>col interval for a30
SCOTT>select what, job, next_date, next_sec, interval
2 from user_jobs
3 where job = 5 ;
2. DBMS_SCHEDULER (10g 이상 사용 가능)
DBMS_JOB 에서는 안되는 외부 스크립트를 실행할 수 있다.
(1) 주요 특징
1) os 생성된 각종 유틸이나 프로그램까지도 실행할 수 있다.
2) 다양한 설정으로 일반적으로 사용하는 자연어를 사용하여 편하게 설정할 수 있다.
3) 데이터베이스 내부 이벤트까지 추적가능하기 때문에 os 나 dbms_job 보다 훨씬 다양하게 작업을 체크하고 수행할 수 있다.
(2) 구성
1) program
이 부분에 dbms_scheduler 가 수행할 프로그램이나 프로시저, 스크립트 등에 대한 정보를 저장해서 별도로 생성할 수 있다.
2) schedule
이 부분에 dbms_scheduler 가 수행할 job 이 실제 수행할 시간이나 실행 주기 등을 별도로 생성할 수 있다.
3) job
새로 생성할 dbms_scheduler 가 수행할 job 이 실제 수행할 시간이나 실행주기 등을 별도로 생성할 수 있다.
(3) DBMS_SCHEDULER 사용하기
1) 신규 job 생성하기
SYS>grant create any job to scott ;
Grant succeeded.
SYS>conn scott/tiger ;
Connected.
SCOTT>
SCOTT>create table job_test1
2 (no number,
3 name varchar2(5),
4 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)
5 values(seq_job_test1_no.nextval, dbms_random.string('a',2)) ;
6 commit ;
7 end ;
8 /
Procedure created.
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.
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 ;
3초마다 한번씩 insert 되는 것이 확인된다.
2) 현재 작동 중인 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
4 and o.object_name = p.job_name
5 and o.object_type = 'JOB'
6* and o.owner = 'SCOTT'
3) 특정 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.
4) 특정 job 을 삭제하기
SCOTT>begin
2 dbms_scheduler.drop_job('insert_job_test1') ;
3 end ;
4 /
PL/SQL procedure successfully completed.
5) DBMS_SCHEDULER.CREATE_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 /
2번 줄은 신규 job 생성
3번 dbms_scheduler 내에서 사용될 job 의 이름 지정
4번 5번 줄에 적히는 항목에 따라 값이 달라짐
5번 실제 실행될 프로그램을 적는 부분
실제 pl/sql 블록을 적을 수 있고
프로시저 이름을 적을 수 있고
os 에 있는 실행파일 이름을 지정할 수 있고
program_name 으로 미리 생성해둔 프로그램 이름을 지정할 수 있고
chained 값으로 생성된 체인을 활용할 수도 있다
4번 만약 5번 줄에 프로시저 이름을 적었다면 4번줄에는
'EXECUTABLE' 로 적어야 한다.
6번 해당 job 이 처음 시작될 시간을 지정하는 곳
systimestamp 는 지금 즉시 시작하라는 뜻이며 만약 지금 시간이 아닌 특정 시간에 시작하도록 지정하고 싶으면
start_date => to_timestamp_tz('2013-04-01 02:00:00 ROK', 'YYYY-MM-DD HH24:MI:SS TZR')
2013 년 4월 1일 새벽 2시에 특정 job 을 시작하라는 의미
종료하는 시간을 지정하고 싶다면
end_date => systimestamp + interval '30' day
end_date => to_timestamp_tz ('2013-04-31 24:00:00 ROK', 'YYYY-MM-DD HH24:MI:SS TZR')
7번 반복할 주기 지정
1) 얼마마다 한번씩 수행
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 ; 매달
repeat_interval => 'FREQ=YEARLY ; 매년 수행
by 절을 사용하면 보다 구체적으로 시간 지정
repeat_interval => 'FREQ=YEARLY; BYWEEKNO=8,16,24,32,40,48; BYDAY=FRI;'
보다 구체적으로 매주 금요일 밤 23:50분
repeat_interval => 'FREQ=WEEKLY; BYDAY=FRI; BYHOUR=23; BYMINUTE=50;'
BY 절에 사용할 수 있는 세부사항
BYMONTH JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC
BYWEEKNO 1 - 53
BYYEARDAY 1 - 366
BYMONTHDAY 1 - 31
BYDATE MMDD 형태로 월일을 직접 지정가능
BYDAY MON, TUE, WED, THU, FRI, SAT, SUN
BYHOUR 0 - 23
BYMINUTE 0 - 59
BYSECOND 0 - 59
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 ;
/
2) 다음 직접 작업 시간까지의 간격 지정하기
repeat_interval => 'SYSDATE + 1' 하루 뒤에 수행
repeat_interval => 'SYSDATE + 30/1440' 1440은 하루를 분으로 환산한 것이니 30분 마다 수행하라는 뜻
6) 외부 스크립트를 생성해서 실행하기
먼저 해당 스크립트를 실행할 권한과 그룹이 지정되어 있는 externaljob.ora 라는 파일을 수정해야 한다.
유저명을 oracle 로 바꾸고, 사용 그룹명을 oracle 계정의 소속 그룹으로 변경한 후 SetUID 를 지정하면 된다.
step1 externaljob.ora 파일의 내용을 수정한다.
$ vi /app/oracle/product/11g/rdbms/admin/externaljob.ora
만약 oracle 계정의 소속 그룹을 모를 경우 아래와 같이 조회하세요
$ id oracle
step2 수정 완료 후 위 파일의 권한을 640 상태로 설정한다. 이 값이 기본값으로 설정되어 있다.
$ ls -l $ORACLE_HOME/rdbms/admin/externaljob.ora
step3 외부 스크립트를 실제로 수행하는 명령인 extjob 파일의 소유자를 변경한다.
SetUID 를 설정하는 방법은 chmod 4750 extjob 인데 기본적으로 설정되어 있다.
파일의 소유자 root 를 oracle 로 변경해주세요.
$ ls -l $ORACLE_HOME/bin/extjob
$ su -
암호:
# cd /app/oracle/product/11g/bin/
# chown oracle extjob
# ls -l extjob
step4 sys 계정으로 오라클에 접속해서 external job 을 수행할 계정에게 적절한 권한을 부여한다.
SYS>grant execute on sys.dbms_scheduler to scott ;
Grant succeeded.
SYS>grant create job to scott ;
Grant succeeded.
SYS>grant create external job to scott ;
step5 수행할 외부 스크립트 생성
[oracle@server108 ~]$ vi mkdir.sh
#!/bin/bash
date=`date +%Y-%m-%d-%H-%M-%S`
mkdir /app/oracle/$date
[oracle@server108 ~]$ chmod 755 mkdir.sh
[oracle@server108 ~]$ sh mkdir.sh
[oracle@server108 ~]$ sh mkdir.sh
[oracle@server108 ~]$
[oracle@server108 ~]$ ls /app/oracle/2013*
스크립트가 정상 작동하는 것을 확인
step6 위 스크립트를 수행하는 schedule 을 생성
SCOTT>begin
2 dbms_scheduler.create_schedule (
3 schedule_name => 'test_dir',
4 start_date => systimestamp,
5 end_date => null,
6 repeat_interval => 'freq=secondly;interval=10');
7 end ;
8 /
step7 위 스크립트를 실행하는 job 생성
SCOTT>begin
2 dbms_scheduler.create_job (
3 job_name => 'test_dir_1',
4 job_type => 'EXECUTABLE',
5 job_action => '/home/oracle/mkdir.sh',
6 schedule_name => 'test_dir' ) ;
7 end ;
8 /
step8 해당 job 을 enable
SCOTT>begin
2 dbms_scheduler.enable('test_dir_1') ;
3 end ;
4 /
step9 해당 job을 실행
SCOTT>exec dbms_scheduler.run_job('test_dir_1') ;
step10 10초 간격으로 생성되는지 확인
[oracle@server108 ~]$ ls /app/oracle/2013*
오라클에서 아래와 같이 조회해도 된다.
SCOTT> select log_date, owner, job_name, status from user_scheduler_job_run_details where owner = 'SCOTT' order by log_date desc ;
위 job 은 테스트 용이므로 확인 후 삭제하여야 한다.
SCOTT>begin
2 dbms_scheduler.drop_job('TEST_DIR_1') ;
3 end ;
4 /
(4) 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 /
5초에 1번을 10초에 1번으로 변경
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, end_date
2 from dba_scheduler_jobs
3 where owner = 'SCOTT' ;
(5) DBMS_SCHEDULER 관리하기
1) 생성된 job을 즉시 실행하기
exec dbms_scheduler.run_job('insert_job_test1') ;
2) job 을 중단하기
exec dbms_scheduler.stop_job(job_name => 'insert_job_test1', force => true) ;
3) job 삭제하기
exec dbms_scheduler.drop_job(job_name => 'insert_job_test1', force => true) ;
해당 job이 실행 중이라도 force 옵션이 true 이기 때문에 강제로 취소시키고 삭제한다.
