오라클 스케줄러
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
1 오라클 JOB, SCHEDULES[편집]
ORACLE JOB과 ORACLE SCHEDULER 가장 큰 차이점이라면 OS상의 crontab에 등재되는 shell프로그램도 ORACLE SCHEDULER에서 작동 가능
1.1 오라클스케쥴러 등록을 위한 권한[편집]
GRANT CREATE ANY JOB TO 계정명;1.2 명령어[편집]
1. DBMS_SCHEDULER.CREATE_PROGRAM
- JOB이 스케쥴러에 맞게 돌면서 실제로 동작하는 프로그램(외부의 shell이나 SP, PLSQL_BLOCK 등) 을 등
2. DBMS_SCHEDULER.CREATE_SCHEDULER
- 주기적으로 돌아갈 스케쥴을 등록
3. DBMS_SCHEDULER.CREATE_JOB
- 수행할 작업을 등록한다.1.3 사용 방법[편집]
1.3.1 1. 동작할 프로그램 등록[편집]
BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM(
program_name => 'STATS_MAIN_BATCH_PROGRAM',
program_action => 'STATS_MAIN_BATCH',
program_type => 'STORED_PROCEDURE',
comments => 'Service desk stats main batch program',
enabled => TRUE);
END;1.3.1.1 1-1. 매개변수가 있는 경우[편집]
BEGIN
/*-------------------------------------------------------------------------------------------------------------------
program_name: 프로그램이름
program_action: 실제 액션이 일어나는 SP(미리 등록되어있어야 한다). 여기서 shell 프로그램등을 돌릴 수도 있다.
program_type: SP 라고 명시
number-of_arguments: 사용할 인수 개수
comments: 코멘트.부가설명
enabled: 사용가능 설정
argument_position: 몇번째 인수인지
argument_name: 인수이름
argument_type: 인수타입
argument_value: 인수값
--------------------------------------------------------------------------------------------------------------------*/
DBMS_SCHEDULER.CREATE_PROGRAM (
PROGRAM_NAME => 'STATS_MAIN_BATCH_PROGRAM'
,PROGRAM_TYPE => 'STORED_PROCEDURE'
,PROGRAM_ACTION => 'STATS_MAIN_BATCH'
,NUMBER_OF_ARGUMENTS => 1
,ENABLED => FALSE
,COMMENTS => 'SERVICE DESK STATS MAIN BATCH PROGRAM');
DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT (
PROGRAM_NAME => 'STATS_MAIN_BATCH_PROGRAM'
,ARGUMENT_POSITION => 1
,ARGUMENT_NAME => 'I_DT'
,ARGUMENT_TYPE => 'VARCHAR2'
,DEFAULT_VALUE => 'TO_CHAR(SYSDATE-1, ''YYYYMMDD'')'
);
DBMS_SCHEDULER.ENABLE(NAME => 'STATS_MAIN_BATCH_PROGRAM');
END;
1.3.2 2. 스케줄 등록[편집]
- 10g 이상
BEGIN
/*-------------------------------------------------------------------------------------------------------------------
schedule_name: 스케쥴 이름
start_date: 스케쥴이 작동을 시작 할 시각. 입력한 시점부터 스케쥴러가 시작된다. AM 03시로 설정함
end_date: 스케쥴이 작동을 멈출 시간.
repeat_interval: 스케쥴이 작동하는 주기. 하루 한번 돌게 설정 매일 AM 03시에 동작함.
comments: 부가설명
--------------------------------------------------------------------------------------------------------------------*/
DBMS_SCHEDULER.CREATE_SCHEDULE(
schedule_name => 'SCHEDULE_DAILY_AM_3_HOUR',
start_date => TRUNC(SYSDATE)+1 +3/24,
end_date => null,
repeat_interval => 'FREQ=DAILY;INTERVAL=1',
comments => 'Every AM 03 HOUR');
END;- 11g, 12c 이상
/*-------------------------------------------------------------------------------------------------------------------
schedule_name : 스케쥴 이름
start_date: 스케쥴이 작동을 시작 할 시각. 입력한 시점부터 스케쥴러가 시작된다. AM 03시로 설정함
end_date: 스케쥴이 작동을 멈출 시간.
repeat_interval: 'FREQ=DAILY/DAILY/;INTERVAL=1; -- 매실행 ' 참고) 매 1회만 시/분/초 실행 BYHOUR/BYMINUTE/BYSECOND=0;
comments:
--------------------------------------------------------------------------------------------------------------------*/
BEGIN
SYS.DBMS_SCHEDULER.CREATE_SCHEDULE
(
schedule_name => 'RTIS_DBA.RTIS_TBL_DAILY_BKUP_SCHE'
,start_date => TO_TIMESTAMP_TZ('2019/05/02 20:50:58.423332 +09:00','yyyy/mm/dd hh24:mi:ss.ff tzh:tzm')
,repeat_interval => 'freq=daily;byhour=01;byminute=10;bysecond=0;'
,end_date => NULL
,comments => '매일 01:10 JOB 실행'
);
END;
/1.3.3 3. JOB 등록, 실제 수행 될 작업으로 스케쥴러와 프로그램 명시[편집]
BEGIN
/*-------------------------------------------------------------------------------------------------------------------
job_name: 작업이름
program_name: 구동 될 프로그램이름 명시. 위에서 등록한 프로그램이름을 적어준다.
schedule_name: 어떤 스케쥴러가 돌면서 이 작업을 수행 할 것인가. 위에 등록한 스케쥴러이름을 적어준다.
job_class: JOB을 Class 라는 단위로 그룹핑해서 관리한다. 많은 양의 스케쥴러 관리를 위해 추가 할 수 있다.
enabled: 사용가능 설정
comments: 코멘트.부가설명
--------------------------------------------------------------------------------------------------------------------*/
DBMS_SCHEDULER.CREATE_JOB (
job_name =>'STATS_MAIN_BATCH_JOB',
program_name =>'STATS_MAIN_BATCH_PROGRAM',
schedule_name =>'SCHEDULE_DAILY_AM_3_HOUR',
comments => 'Service desk stats main batch program',
--job_class =>'SCHEDULER_JOB_CLASS',
enabled =>TRUE);
END;1.3.4 4. 스케줄러 삭제[편집]
execute dbms_scheduler.drop_job('FRG_BALANCE_HIST_UPDATE_JOB',false);
execute dbms_scheduler.drop_program('FRG_BALANCE_HIST_UPDATE_PRG',false);
execute dbms_scheduler.drop_schedule('SCHEDULE_30_MIN',false);
*.job먼저 지우고 program 삭제
*.program 먼저 지우려고 하면 종속된 객체라면서 안지워짐.
BEGIN
DBMS_SCHEDULER.DROP_JOB(
JOB_NAME => 'STATS_MAIN_BATCH_JOB',
FORCE => FALSE);
END;
BEGIN
DBMS_SCHEDULER.DROP_PROGRAM(
PROGRAM_NAME => 'STATS_MAIN_BATCH_PROGRAM',
FORCE => FALSE);
END;1.3.5 5. 등록 정보 확인[편집]
SELECT * FROM DBA_SCHEDULER_JOBS; --등록된 job
SELECT * FROM DBA_SCHEDULER_JOB_ARGS; --job의 arguments
SELECT * FROM DBA_SCHEDULER_RUNNING_JOBS; --현재 running중인 job들의정보
SELECT * FROM DBA_SCHEDULER_JOB_LOG; --job의 log
SELECT * FROM DBA_SCHEDULER_JOB_RUN_DETAILS; --job의수행된정보및Error 정보
SELECT * FROM DBA_SCHEDULER_PROGRAMS; -- 등록된 Program
SELECT * FROM DBA_SCHEDULER_PROGRAM_ARGS; -- 프로그램의 매게변수
SELECT * FROM DBA_SCHEDULER_SCHEDULES; --등록된 스케쥴러1.3.6 참고사항[편집]
# JOB_CLASS 설정
BEGIN
DBMS_SCHEDULER.CREATE_JOB_CLASS (
job_class_name =>'SCHEDULER_JOB_CLASS',
logging_level =>DBMS_SCHEDULER.LOGGING_FULL,
log_history =>1,
comments =>'SCHEDULER_JOB_CLASS');
END;- PROGRAM, SCHEDULE, JOB 세 개로 세분화하여 등록하는 형태.
- 하지만 세개 모두 등록하지 않고 CREATE_JOB 한 개만 등록하여 사용하는 방법도 있다.
- DBA_SCHEDULER_JOBS/USER_SCHEDULER_JOBS
select * from user_scheduler_jobs;- - CREATE_PROGRAM 에서 사용했던 program_type, program_action과 같이 job_type, job_action 컬럼 존재
- - CREATE_SCHEDULE에서 사용했던 start_date, end_date, repeat_interval이 존재.
- API
http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_sched.htm
1.4 스케줄 등록 예시[편집]
-- 샘플 테이블 생성
CREATE TABLE CHECK_SCHEDULE ( INST_ID NUMBER, ACT_DATE DATE DEFAULT SYSDATE, ACT_DESC VARCHAR2(100)) ;
-- 샘플 프로시져 생성
CREATE OR REPLACE PROCEDURE OPS$ORACLE.DO_SCHEDULE_PROG IS V_SID NUMBER ; V_INST NUMBER ;
BEGIN SELECT INSTANCE_NUMBER INTO V_INST FROM V$INSTANCE ;
INSERT INTO CHECK_SCHEDULE ( INST_ID, ACT_DATE, ACT_DESC ) VALUES ( V_INST, SYSDATE, 'DO SCHEDULEING') ;
END ;
-- 스케줄 등록
EXEC DBMS_SCHEDULER.CREATE_SCHEDULE(schedule_name=>'CHK_SCHE',repeat_interval=>'FREQ=MINUTELY');
-- 스케줄 확인
SELECT SCHEDULE_NAME, REPEAT_INTERVAL
FROM DBA_SCHEDULER_SCHEDULES
WHERE OWNER ='OPS$ORACLE'
AND SCHEDULE_NAME='CHK_SCHE';
SCHEDULE_NAME REPEAT_INTERVAL CHK_SCHE FREQ=MINUTELY
-- 프로그램 등록
EXEC DBMS_SCHEDULER.CREATE_PROGRAM(
PROGRAM_NAME=>'DO_SCHEDULE_PROGRAM'
, PROGRAM_TYPE=>'STORED_PROCEDURE'
, PROGRAM_ACTION=>'DO_SCHEDULE_PROG');
-- 프로그램 Enable
EXEC DBMS_SCHEDULER.ENABLE('DO_SCHEDULE_PROGRAM');
-- 프로그램 등록 확인
SELECT PROGRAM_NAME, PROGRAM_ACTION, ENABLED
FROM DBA_SCHEDULER_PROGRAMS
WHERE OWNER ='OPS$ORACLE' AND PROGRAM_NAME='DO_SCHEDULE_PROGRAM';
-- 잡 등록
EXEC DBMS_SCHEDULER.CREATE_JOB(JOB_NAME=>'CHK_SCHE_JOB' ,PROGRAM_NAME=>'DO_SCHEDULE_PROGRAM' ,SCHEDULE_NAME=>'CHK_SCHE' ,AUTO_DROP=>FALSE);
-- 잡 Enable
EXEC DBMS_SCHEDULER.ENABLE('CHK_SCHE_JOB');
-- 잡 등록 확인
SELECT JOB_NAME, PROGRAM_NAME, SCHEDULE_NAME, ENABLED
FROM DBA_SCHEDULER_JOBS WHERE OWNER ='OPS$ORACLE' AND JOB_NAME='CHK_SCHE_JOB';
-- 작업 결과 확인
SELECT LOG_DATE, ACTUAL_START_DATE, JOB_NAME, STATUS
FROM DBA_SCHEDULER_JOB_RUN_DETAILS
WHERE JOB_NAME ='CHK_SCHE_JOB';
-- 실제 결과 확인
SELECT * FROM CHECK_SCHEDULE ;
-- 등록된 job 확인
SELECT * FROM DBA_SCHEDULER_JOBS;
-- job의 arguments
SELECT * FROM DBA_SCHEDULER_JOB_ARGS;
--현재 running중인 job들의정보
SELECT * FROM DBA_SCHEDULER_RUNNING_JOBS;
--job의 log
SELECT * FROM DBA_SCHEDULER_JOB_LOG;
--job의수행된정보및Error 정보
SELECT * FROM DBA_SCHEDULER_JOB_RUN_DETAILS;
-- 등록된 Program
SELECT * FROM DBA_SCHEDULER_PROGRAMS;
-- 프로그램의 매개변수
SELECT * FROM DBA_SCHEDULER_PROGRAM_ARGS;
--등록된 스케쥴러
SELECT * FROM DBA_SCHEDULER_SCHEDULES;
-- 스케쥴 삭제
BEGIN
DBMS_SCHEDULER.DROP_JOB(
JOB_NAME => 'SCH_PROC_MSG_STATE',
FORCE => false);
END ;
-- 스케쥴 시작
EXECUTE DBMS_SCHEDULER.ENABLE('SCH_PROC_MSG_STATE');
-- 스케쥴 정지
EXECUTE DBMS_SCHEDULER.DISABLE('SCH_PROC_MSG_STATE');
DBMS_JOB.submit : job 등록
DBMS_JOB.remove : 제거
DBMS_JOB.change : 변경
DBMS_JOB.next_date : job의 다음 수행시간 변경
DBMS_JOB.interval : job의 실행 cycle 지정
DBMS_JOB.what : job 수행 으로 등록된 object 를 변경
DBMS_JOB.run : job을 수동으로 실행[등록 예제]
DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => '실행할 object'
,next_date => to_date('17-11-2007 09:00:00','dd/mm/yyyy hh24:mi:ss')
,interval => 'TRUNC(SYSDATE) + 1 + 9/24'
,no_parse => TRUE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x)); -- 이부분은 job큐의 번호가 됩니다.
END;BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'SCH_PROC_MSG_STATE_TO_DAY',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN MEM_NPRO.TEST_PROC (TO_CHAR(SYSDATE , ''YYYYMMDD'')); END;',
start_date => TO_DATE('2011-12-14 17:30:00' , 'YYYY-MM-DD HH24:MI:SS'),
repeat_interval => 'FREQ=MINUTELY;INTERVAL=30',
end_date => NULL,
enabled => TRUE,
comments => 'HJ SCH_PROC_MSG_STATE_TO_DAY');
END;
1.4.1 RAC에서 2번 노드에서만 실행[편집]
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
,attribute => 'INSTANCE_ID'
,value => 2);[인터벌 설정법]
Execute daily 'SYSDATE + 1'
Execute once per week 'SYSDATE + 7'
Execute hourly 'SYSDATE + 1/24'
Execute every 10 min. 'SYSDATE + 10/1440'
Execute every 30 sec. 'SYSDATE + 30/86400'
Do not re-execute NULL
1. 10분간격으로 실행
SYSDATE + 1/24/6
2. 현재 시간으로 부터 다음 날 현재 시간에 실행 (매일)
SYSDATE + 1 -- 지금이 오후3시면 다음날 오후 3시 에 매일매일 실행됩니다.
3. 매일 새벽 5시
TRUNC(SYSDATE) + 1 + 5 / 24
4. 매일 밤 10시
TRUNC(SYSDATE) + 20 / 24