Oracle Scheduler, MySQL Event Scheduler

데이터베이스에서 일정 주기마다 데이터 처리를 해줘야 하는 상황이 발생할 수 있다.

이걸 찾아보게 된 계기 역시 일정 시간이 지난 데이터들을 주기적으로 삭제해주기 위해서였다.

이렇게 주기적으로 처리하도록 스케줄러를 활용해 처리할 수 있다.

 

Oracle Scheduler

오라클에서는 10g 이전에는 job 으로 사용했었고 그 이후에는 Scheduler를 지원한다고 한다.

물론 10g 이상이라고 해서 job을 사용할 수 없는것은 아니지만 job의 문제점을 보완해 나온 패키지이기 때문에

더 많이 사용한다고 한다.

 

Oracle job의 경우는 PLSQL_BLOCK과 STORED_PRECEDURE만 구동할 수 있으며

Scheduler에서는 OS 상의 crontab에 등재되는 쉘 프로그램도 사용할 수 있다는 차이가 있다.

 

Oracle Scheduler의 생성 문법은 아래와 같다.

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => '스케줄러 이름'
    , job_type => '스케줄러 타입'
    , job_action => '스케줄러 동작시 실행할 프로그램'
    , start_date => 첫 동작 시점
    , repeat_interval => '반복 시점'
    , end_date => 종료 시점
    , enabled => 활성화 여부
    , auto_drop => 스케줄러 종료시 drop 여부
    , comment => 'discription'
  );
END;

Oracle Scheduler 생성은 BEGIN과 END 사이에 DBMS_SCHEDULER.CREATE_JOB( )으로 생성하게 된다.

 

job_name

job_name은 적혀있는대로 스케줄러명을 의미한다.

 

 

job_type

job_type은 스케줄러의 타입인데 아래와 같은 타입이 존재한다.

• PLSQL_BLOCK : PL/SQL 블럭 형태
• STORED_PROCEDURE : 내장 프로시저
• EXECUTABLE : 실행 파일

이 스케줄러가 동작하면서 실행할 프로그램이 어떤것이냐에 따라 job_type 설정을 해주면 된다.

보통 예제들로 나오는것들은 프로시저로 처리하는 예제가 많았다.

간단한 쿼리문만 실행하도록 하게 한다면 PLSQL_BLOCK을 사용하면 되고

프로시저를 생성해 그 프로시저를 호출하도록 할것이면 STORED_PROCEDURE를 job_type으로 지정하는 형태로 사용하면 된다.

 

 

job_action

job_action은 스케줄러가 동작할 때 실행할 프로그램이다.

위에서 언급한것처럼 job_type에 맞게 작성하면된다.

PLSQL_BLOCK이라면 여기에 쿼리문을 작성해주면 되고 STORED_PROCEDURE라면 프로시저명을 작성해주면 된다.

 

 

start_date, repeat_interval

start_date는 스케줄러가 동작할 시점이고 repeat_interval은 그 다음으로 반복할 시간대를 의미한다.

이 두가지를 묶은데에는 이유가 있다.

둘다 시간대에 대한 설정이기 때문이다.

 

start_date와 repeat_interval에서 지금 눈에 보이기만 하는 차이는 ' ' 가 있느냐 없느냐 차이정도다.

왠지 모르겠지만 start_date에서는 그냥 사용하면 되고 repeat_interval은 ' '안에 작성해야 한다.

그럼 스케줄러 생성과 동시에 바로 동작하도록 하기 위해서는 start_date에

sysdate    이렇게 작성해주면 된다.

 

이제 여기서부터가 생각보다 언급되지 않아서 여기저기 좀 찾아봤었다.

내가 작성하게 될 스케줄러는 일정기간이 지난 데이터 삭제만 처리하는 내용이기 때문에 자정에 처리하는것이 목적이었다.

그래서 찾아보니 방법은 TRUNC(sysdate+1) + 0/24  이렇게 사용하는것이었다.

처음 딱 보자마자 sysdate+1은 다음날을 의미하는건 알겠고 24시간중 0시를 의미하나?? 이생각이 들었다.

다행히 내 생각이 맞았지만 문제는 이 다음이었다...

 

시작시간을 어떠한 시간으로 고정하는것이 아닌 1분뒤, 1시간뒤 이렇게 설정해야 한다면?

보통 포스팅들은 1분 뒤 시작하도록 하는 예제가 많았다.

이때 작성은 sysdate + 1/24/60 이렇게 작성한다.

그럼 1분뒤 실행이 된다.

그럼 1초단위는?

sysdate + 1/24/60/60 이렇게 작성한다.

즉, 1/24는 한시간단위, 1/24/60은 1분단위, 1/24/60/60은 1초단위다.

포스팅을 보면서 알게된건 1/24/60/10 이렇게 작성하게 되면 6초가 된다.

그럼 30초 뒤에 작동하도록 작성한다면

sysdate + 1/24/60/2 이렇게 작성하는 방법과 sysdate + 30/24/60/60 이 방법이 있다.

개인적으로는 후자가 더 좋은듯.............

 

그리고 또 한가지 방법으로는 to_date를 사용하는 방법이 있다.

to_date('21/04/2022 18:00:00', 'dd/mm/yyyy hh24:mi:ss')

이렇게 날짜와 시간을 명시해 처리하는 방법도 있다.

 

start_date 작성을 정리하자면 아래와 같다.

1. TRUNC(sysdate+1) + 0/24 : 다음날 몇시에 동작할지 명시할 수 있다.
2. sysdate + 1/24 : 한시간 뒤 동작. 현재 시간으로부터 시간, 분, 초 단위 이후에 동작하도록 할 수 있다. 단, 이떄는 TRUNC 함수를 사용할 수 없다. 사용하게 되면 생성 직후 동작한다.
3. to_date('21/04/2022 18:00:00', 'dd/mm/yyyy hh24:mi:ss') : 날짜와 시간을 명시해 동작하도록 할 수 있다.

 

repeat_interval에서도 마찬가지로 사용이 가능하지만 더 편리한 방법이 존재한다.

'FREQ=DAILY;INTERVAL=1' 이렇게 작성하게 되면 다음날 같은 시간에 반복하게 된다.

FREQ의 경우는 SECONDLY, MINUTELY, HOURLY, DAILY, WEEKLY, MONTHLY, YEARLY 이렇게 존재한다.

앞에서부터 초, 분, 시, 일, 주, 월, 연 단위다.

INTERVAL은 주기를 의미한다.

위 예시 그대로 보면 1일뒤 반복을 의미하고 FREQ=DAILY;INTERVAL=2 이렇게 하면 2일에 한번 반복하게 된다.

일단위, 주단위 형태로 사용하게 된다면 이렇게 사용하는게 더 편한것 같다.

 

end_date

end_date는 스케줄러의 종료 시점을 의미한다.

start_date와 같은 형태로 작성하면 된다.

 

 

enabled

enabled는 활성화 여부를 의미한다.

생략해도 문제가 발생하지 않지만 default가 false로 되어있기 때문에 따로 활성화를 시켜줘야 한다.

생성하자마자 바로 활성화를 원한다면 true로 설정해주면 된다.

 

만약 생략한 경우는 아래와 같이 활성화를 한다.

-- 활성화
BEGIN
  DBMS_SCHEDULER.ENABLE(name=>'스케줄러명');
END;

-- 비활성화
BEGIN
  DBMS_SCHEDULER.DISABLE(name=>'스케줄러명', force => true);
END;

활성화가 있으면 당연히 비활성화도 있기 때문에 위와 같이 사용하면 된다.

 

 

auto_drop

auto_drop의 경우는 포스팅에서 따로 설명을 찾지 못했다.

Oracle docs에서는 간단하게 

If TRUE (the default), indicates that the job should be dropped once completed

라고만 나온다.

작업 완료 후 자동으로 삭제된다는 것.근데 oraexcel 이라는 사이트에서는 좀 더 상세한 설명을 볼 수 있었다.추가된 내용으로는 작업이 완료된 시점으로 간주하는 경우가 있었다.그 시점으로는 종료날짜가 지난경우, max_runs에 도달한 경우, max_failures 횟수에 도달하는 경우이다.max_runs는 최대 실행횟수, max_failures는 최대 실패 횟수인데 설정하는 방법은 아래와 같다.

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => '스케줄러 이름'
    , job_type => '스케줄러 타입'
    , job_action => '스케줄러 동작시 실행할 프로그램'
    , start_date => 첫 동작 시점
    , repeat_interval => '반복 시점'
    , end_date => 종료 시점
    , enabled => 활성화 여부
    , auto_drop => 스케줄러 종료시 drop 여부
    , comment => 'discription'
  );
  DBMS_SCHEDULER.SET_ATTRIBUTE(
    name => '스케줄러명'
    , attribute => 'MAX_RUNS'
    , value => 3
  );
  DBMS_SCHEDULER.SET_ATTRIBUTE(
    name => '스케줄러명'
    , attribute => 'MAX_FAILURES'
    , value => 5
  );
END;

이렇게 설정하게 되면 max_runs는 3, max_failures는 5로 설정이 된다.

이걸 확인해보는 방법은 

SELECT * from user_scheduler_jobs;

이렇게 확인할 수 있다.

 

 

comments

comments는 그냥 이 스케줄러에 대한 코멘트를 달아놓는 것이다.

discription과 동일하다고 볼 수 있다.

 

 

그 외

만약 잡을 생성할 때 권한 오류가 발생한다면 해당 사용자에게 권한을 부여해줘야 한다.

권한은 sys 계정으로 부여하면 되고 아래 쿼리를 실행한다.

GRANT CREATE ANY JOB TO 계정명;

이렇게 부여할 수 있다.

 

잡 생성 내역에 대한 확인 및 실행내역은 아래 쿼리로 확인할 수 있다.

-- 잡 생성내역
SELECT * FROM user_scheduler_jobs;

-- 잡 실행내역
SELECT * FROM user_scheduler_job_log;

-- 잡 실행내역 좀 더 상세하게
SELECT * FROM user_scheduler_job_run_details;

생성내역에서는 설정 내역들을 볼 수 있고

실행내역에서는 간단한 실행 내역들을 볼 수 있다.

그리고 좀 더 상세하게 run_details로 실행하게 되면 오류가 발생했을 때 로그까지 확인이 가능하다.

 

마지막으로 삭제 쿼리는 아래와 같이 작성한다.

BEGIN
  DBMS_SCHEDULER.DROP_JOB(job_name => '스케줄러명', defer => false, force => false);
END;

 

 

 

MySQL Event Scheduler

MySQL의 Event Scheduler는 Oracle의 Job Scheduler보다 훨씬 간단하게 사용할 수 있다.

CREATE EVENT test_event
ON SCHEDULER EVERY 1 MINUTE
STARTS '2022-04-21 18:00:00'
COMMENT '테스트 이벤트'
DO
CALL procedure_test();

 

CREATE EVENT

이벤트 스케줄러 생성 구문이고 뒤에는 스케줄러명을 작성해주면 된다.

 

 

ON SCHEDULER EVERY 1 MINUTE

스케줄러의 반복 주기를 의미한다.

EVERY 1 MINUTE라는 것은 1분에 한번씩 반복한다는 의미이다.

오라클에서와 마찬가지로 SECOND, MINUTE, HOUR, DAY, WEEK, MONTH로 사용이 가능하다.

찾아보다가 stackoverflow에서 알게된건데

1분 30초 간격으로 반복하고 싶다면?

ON SCHEDULER EVERY '1:30' MINUTE_SECOND

이렇게 쓰면 1분 30초 간격으로 설정할 수 있다.

 

 

STARTS

시작 시점을 의미한다.

예제처럼 시간대를 명시해도 되지만 curdate()로 생성 즉시 실행하도록 할 수 있다.

 

 

COMMENT

오라클에서의 comment와 마찬가지로 설명을 적는 부분이다.

 

 

DO

실행 구문을 의미한다.

프로시저를 실행할거면 CALL 프로시저명(); 형태로 작성하면 되고

쿼리문을 실행할거면

쿼리문을 아래에 작성해주면 된다.

 

 

그 외

이벤트 스케줄러를 조회하는 쿼리는 아래와 같다.

SHOW EVENTS;

SELECT * FROM information_schema.events;

그리고 이벤트 스케줄러 삭제 쿼리는 아래와 같다.

DROP EVENT 스케줄러명;

 

 

 

 

oracle과 mysql 두개만 써봤다 보니 두가지에 한정에서 스케줄러 사용법을 좀 공부했는데

아무래도 mysql이 더 편해보이긴 한다...

근데 또 세세하게 설정하자면 그게 그거일 것 같기도 하고..

 

 

 

레퍼런스

● Oracle Scheduler

[Oracle] 스케줄러 사용하기

[오라클] 잡 생성

[Database] Oracle 스케쥴, 잡

 

AUTO_DROP Parameter DBMS_SCHEDULER.CREATE_JOB Oracle 12cr1

 

Creating and Managing Schedules to Define Jobs

 

● MySQL Event Scheduler

[MySQL]개발자라면 반드시 알아야하는 EVENT SCHEDULE

Mysql 이벤트 스케쥴러 (Event Scheduler)

How to schedule a MySQL Event on every XX:50 Seconds?