[오라클 실무] DBMS_SCHEDULER로 프로시저를 6시간 단위 실행하기 - 동적 인자 전달 완벽 예제

[오라클 실무] DBMS_SCHEDULER로 프로시저를 6시간 단위 실행하기 - 동적 인자 전달 완벽 예제

테스트 환경: Oracle 12c / 19c / 21c

DBMS_SCHEDULER로 배치 작업을 등록할 때 가장 자주 막히는 부분이 "실행할 때마다 바뀌는 인자(예: 어제/오늘 날짜)를 어떻게 동적으로 전달하느냐" 입니다.

SET_JOB_ARGUMENT_VALUE로 인자를 박으면 정적 값이 고정되어 매일 같은 값으로 실행되고, 그렇다고 매번 작업을 다시 만들 수도 없습니다. 정답은 PLSQL_BLOCK 타입으로 호출하면서 인자 평가를 작업 액션 안으로 넣는 것입니다.

이번 글에서는 실제 운영 환경에서 자주 나오는 케이스 — "PROC_TAX_PURCHASE_A10(어제, 오늘)을 매일 04시, 10시, 16시, 22시에 실행" — 를 예제로, 등록부터 검증, 운영까지 한 번에 정리했습니다.

급하신 분은 전체 코드 한 번에 보기 섹션부터 보세요.

 

요구사항 정리

먼저 만들 작업의 사양을 정리해 봅니다.

항목 내용

실행할 프로시저	PROC_TAX_PURCHASE_A10
인자 1	어제 날짜 (YYYYMMDD 8자리 문자열)
인자 2	오늘 날짜 (YYYYMMDD 8자리 문자열)
실행 주기	매일 04시, 10시, 16시, 22시 (6시간 간격)
인자 평가 시점	매 실행 시점 (등록 시점 아님)

핵심은 마지막 줄입니다. 작업을 한 번 등록하지만, 실행될 때마다 그 시점의 SYSDATE로 인자가 다시 계산되어야 합니다.

 

전체 코드 한 번에 보기

복붙해서 바로 사용하실 수 있는 완성 코드입니다.

BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name        => 'JOB_TAX_PURCHASE_A10',
        job_type        => 'PLSQL_BLOCK',
        job_action      => q'[
            DECLARE
                v_yest  VARCHAR2(8) := TO_CHAR(SYSDATE - 1, 'YYYYMMDD');
                v_today VARCHAR2(8) := TO_CHAR(SYSDATE,     'YYYYMMDD');
            BEGIN
                PROC_TAX_PURCHASE_A10(v_yest, v_today);
            END;
        ]',
        start_date      => SYSTIMESTAMP,
        repeat_interval => 'FREQ=DAILY;BYHOUR=4,10,16,22;BYMINUTE=0;BYSECOND=0',
        enabled         => TRUE,
        comments        => '세금/구매 데이터 처리 - 매일 04, 10, 16, 22시 실행'
    );
END;
/

위 코드 하나만 실행하면 작업 등록이 끝납니다. 이제 각 부분을 자세히 보겠습니다.

 

코드 한 줄씩 해부하기

1) job_name - 작업 이름

job_name => 'JOB_TAX_PURCHASE_A10'

작업 이름은 스키마 내에서 유일해야 합니다. 접두사 JOB_을 일관되게 붙이면 dba_scheduler_jobs 조회 시 한 번에 모아 볼 수 있어 운영에 유리합니다.

2) job_type - 작업 유형

job_type => 'PLSQL_BLOCK'

STORED_PROCEDURE를 쓰면 SET_JOB_ARGUMENT_VALUE로 인자를 박아야 해서 동적 인자 전달이 사실상 불가능합니다. 인자가 매번 달라져야 하는 경우 PLSQL_BLOCK이 답입니다.

유형 동적 인자 사용 시점

PLSQL_BLOCK	✅ 가능	인자가 매 실행마다 달라질 때 (이 경우)
STORED_PROCEDURE	❌ 정적만	인자가 고정될 때
EXECUTABLE	✅ 가능	OS 셸 스크립트 실행

3) job_action - 핵심 ★

job_action => q'[
    DECLARE
        v_yest  VARCHAR2(8) := TO_CHAR(SYSDATE - 1, 'YYYYMMDD');
        v_today VARCHAR2(8) := TO_CHAR(SYSDATE,     'YYYYMMDD');
    BEGIN
        PROC_TAX_PURCHASE_A10(v_yest, v_today);
    END;
]'

이 글에서 가장 중요한 부분입니다. 두 가지 디테일이 들어있습니다.

디테일 1: q'[ ]' 인용 표기법 사용

job_action은 VARCHAR2 문자열입니다. 안에 작은따옴표가 들어가면 보통 두 번 써야 하는데(''YYYYMMDD''), 코드가 지저분해집니다. Oracle 10g부터 지원하는 q-quoting 문법을 쓰면 작은따옴표를 그대로 쓸 수 있습니다.

-- ❌ 일반 방식 (작은따옴표 두 번 - 가독성 떨어짐)
job_action => 'BEGIN PROC_TAX_PURCHASE_A10(TO_CHAR(SYSDATE-1,''YYYYMMDD''), TO_CHAR(SYSDATE,''YYYYMMDD'')); END;'

-- ✅ q-quoting (작은따옴표 그대로 - 깔끔)
job_action => q'[BEGIN PROC_TAX_PURCHASE_A10(TO_CHAR(SYSDATE-1,'YYYYMMDD'), TO_CHAR(SYSDATE,'YYYYMMDD')); END;]'

긴 PL/SQL 블록일수록 차이가 큽니다. 운영 코드에서는 q-quoting 사용을 강력히 권장합니다.

디테일 2: 인자를 변수에 먼저 담는 패턴

직접 호출도 가능합니다.

BEGIN
    PROC_TAX_PURCHASE_A10(
        TO_CHAR(SYSDATE-1, 'YYYYMMDD'),
        TO_CHAR(SYSDATE,   'YYYYMMDD')
    );
END;

하지만 변수에 먼저 받아두는 패턴이 두 가지 이유로 더 안전합니다.

첫째, 자정 경계 문제 방지. 두 SYSDATE 호출 사이에 자정이 지나면 어제와 오늘 값이 어긋날 수 있습니다. 새벽 04시 실행에서는 거의 발생하지 않지만, 변수에 한 번에 담아두면 인자 일관성이 보장됩니다.

둘째, 로그 남기기 쉬움. 디버깅이 필요할 때 변수 값을 DBMS_OUTPUT.PUT_LINE이나 로그 테이블에 기록하기 편합니다.

-- 디버깅 가능한 확장 패턴
DECLARE
    v_yest  VARCHAR2(8) := TO_CHAR(SYSDATE - 1, 'YYYYMMDD');
    v_today VARCHAR2(8) := TO_CHAR(SYSDATE,     'YYYYMMDD');
BEGIN
    INSERT INTO batch_log VALUES (SYSDATE, 'A10 시작', v_yest, v_today);
    PROC_TAX_PURCHASE_A10(v_yest, v_today);
    INSERT INTO batch_log VALUES (SYSDATE, 'A10 종료', v_yest, v_today);
    COMMIT;
END;

4) repeat_interval - 6시간 간격 표현

repeat_interval => 'FREQ=DAILY;BYHOUR=4,10,16,22;BYMINUTE=0;BYSECOND=0'

이 표현식이 "매일 04:00, 10:00, 16:00, 22:00 실행"을 의미합니다. 각 절을 분해하면:

절 의미

FREQ=DAILY	일 단위 반복
BYHOUR=4,10,16,22	04시, 10시, 16시, 22시
BYMINUTE=0	0분 (정각)
BYSECOND=0	0초

왜 FREQ=HOURLY;INTERVAL=6을 안 쓰는가?

FREQ=HOURLY;INTERVAL=6도 6시간마다 실행되긴 하지만, 시작 시각이 start_date에 따라 결정되어 "정확히 04/10/16/22시"가 보장되지 않습니다. 예를 들어 11시에 등록하면 11시, 17시, 23시, 05시로 실행될 수 있습니다.

시각을 명시적으로 지정해야 한다면 반드시 BYHOUR를 쓰세요. 이게 운영자가 가장 자주 실수하는 부분입니다.

BYMINUTE=0;BYSECOND=0을 꼭 넣어야 하는 이유

생략하면 작업 등록 시각의 분/초가 매 실행에도 적용됩니다. 11:23:45에 등록했다면 04:23:45, 10:23:45 식으로 실행됩니다. 정각 실행이 필요하면 반드시 명시하세요.

5) start_date - 시작 시각

start_date => SYSTIMESTAMP

SYSTIMESTAMP로 지정하면 "지금부터 활성"이라는 뜻입니다. 다음 실행은 다음 BYHOUR 시각에 자동으로 잡힙니다.

타임존 주의 (★ 19c 이상)

19c부터 DBMS_SCHEDULER가 세션 타임존을 사용하면서 업그레이드 후 실행 시각이 어긋나는 사례가 있습니다. 운영 환경이라면 명시적 타임존 지정이 안전합니다.

start_date => TO_TIMESTAMP_TZ('2026-06-02 04:00:00 Asia/Seoul',
                              'YYYY-MM-DD HH24:MI:SS TZR')

6) enabled - 활성화 여부

enabled => TRUE

DBMS_SCHEDULER의 기본값은 FALSE(비활성) 입니다. 명시적으로 TRUE를 주지 않으면 작업은 등록되지만 절대 실행되지 않습니다. 가장 흔한 함정 중 하나입니다.

 

등록 직후 검증 - 3단계

운영 환경에 작업을 등록했다면 다음 3가지를 반드시 확인하세요.

단계 1: 작업이 정상 등록되었나

SELECT job_name, state, enabled, 
       TO_CHAR(next_run_date, 'YYYY-MM-DD HH24:MI:SS') AS next_run,
       repeat_interval
FROM   user_scheduler_jobs
WHERE  job_name = 'JOB_TAX_PURCHASE_A10';

확인 포인트:

• state = SCHEDULED (정상)
• enabled = TRUE
• next_run = 가장 가까운 04/10/16/22시 중 하나

단계 2: 다음 10번의 실행 시각 미리보기

스케줄이 의도대로 잡혔는지 등록 전에 검증하는 가장 좋은 방법입니다.

DECLARE
    v_start   TIMESTAMP WITH TIME ZONE := SYSTIMESTAMP;
    v_next    TIMESTAMP WITH TIME ZONE;
BEGIN
    FOR i IN 1..10 LOOP
        DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING(
            calendar_string   => 'FREQ=DAILY;BYHOUR=4,10,16,22;BYMINUTE=0;BYSECOND=0',
            start_date        => SYSTIMESTAMP,
            return_date_after => v_start,
            next_run_date     => v_next
        );
        DBMS_OUTPUT.PUT_LINE(i || '회차: ' || TO_CHAR(v_next, 'YYYY-MM-DD HH24:MI:SS TZR'));
        v_start := v_next;
    END LOOP;
END;
/

출력 예시:

1회차: 2026-06-01 22:00:00 ASIA/SEOUL
2회차: 2026-06-02 04:00:00 ASIA/SEOUL
3회차: 2026-06-02 10:00:00 ASIA/SEOUL
4회차: 2026-06-02 16:00:00 ASIA/SEOUL
...

이 검증을 본 등록 전에 미리 실행하면 사고를 95% 막을 수 있습니다.

단계 3: 수동으로 한 번 실행 테스트

스케줄에 따른 첫 실행을 기다리지 말고, 즉시 한 번 돌려서 프로시저 자체에 문제가 없는지 확인합니다.

EXEC DBMS_SCHEDULER.RUN_JOB('JOB_TAX_PURCHASE_A10', use_current_session => FALSE);

use_current_session => FALSE로 주면 실제 스케줄러 워커가 실행하는 환경과 동일하게 동작합니다. 권한, 세션 설정 등 문제를 미리 잡을 수 있습니다.

 

실행 이력 모니터링

작업 등록만큼 중요한 게 사후 모니터링입니다. SCHEDULED 상태인데도 실제로는 안 돌고 있는 경우가 운영 환경에서 자주 발생합니다.

일별 실행 결과 확인

SELECT TO_CHAR(actual_start_date, 'YYYY-MM-DD HH24:MI:SS') AS start_time,
       status,
       run_duration,
       error#,
       SUBSTR(errors, 1, 200) AS error_message
FROM   user_scheduler_job_run_details
WHERE  job_name = 'JOB_TAX_PURCHASE_A10'
ORDER  BY actual_start_date DESC
FETCH  FIRST 20 ROWS ONLY;

status 가능 값:

• SUCCEEDED: 정상 완료
• FAILED: 실패 (error#, errors에서 사유 확인)
• STOPPED: 외부에서 중단됨
• BROKEN: 연속 실패로 자동 비활성화됨

실패 알림 자동화 (★ 실무 핵심)

운영 환경에서는 작업이 실패했을 때 즉시 알림이 필요합니다. DBMS_SCHEDULER는 이메일 알림을 기본 지원합니다.

-- 1) 메일 서버 설정 (한 번만)
BEGIN
    DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('email_server', 'smtp.company.com:25');
    DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('email_sender', 'dba@company.com');
END;
/

-- 2) 실패 시 알림 등록
BEGIN
    DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION(
        job_name   => 'JOB_TAX_PURCHASE_A10',
        recipients => 'dba-team@company.com',
        events     => 'JOB_FAILED, JOB_BROKEN, JOB_SCH_LIM_REACHED'
    );
END;
/

이렇게 해두면 작업이 실패한 순간 담당자에게 메일이 발송됩니다.

 

운영 관리 명령어 모음

자주 쓰는 명령어를 모았습니다. 운영 매뉴얼에 그대로 활용 가능합니다.

-- 즉시 실행 (수동)
EXEC DBMS_SCHEDULER.RUN_JOB('JOB_TAX_PURCHASE_A10');

-- 임시 비활성화 (점검 시)
EXEC DBMS_SCHEDULER.DISABLE('JOB_TAX_PURCHASE_A10');

-- 다시 활성화
EXEC DBMS_SCHEDULER.ENABLE('JOB_TAX_PURCHASE_A10');

-- 실행 시각 변경 (예: 매일 03, 09, 15, 21시로)
BEGIN
    DBMS_SCHEDULER.SET_ATTRIBUTE(
        name      => 'JOB_TAX_PURCHASE_A10',
        attribute => 'repeat_interval',
        value     => 'FREQ=DAILY;BYHOUR=3,9,15,21;BYMINUTE=0;BYSECOND=0'
    );
END;
/

-- 현재 실행 중인 인스턴스 강제 중지
EXEC DBMS_SCHEDULER.STOP_JOB('JOB_TAX_PURCHASE_A10', force => TRUE);

-- 작업 완전 삭제
EXEC DBMS_SCHEDULER.DROP_JOB('JOB_TAX_PURCHASE_A10');

 

운영 환경 주의사항 5가지

1. 이전 실행이 안 끝났는데 다음 시각이 오면?

DBMS_SCHEDULER는 기본적으로 이전 실행이 완료될 때까지 다음 실행을 대기시킵니다. 06시간 간격이라 6시간 안에 끝나야 다음 사이클이 정상 동작합니다.

만약 작업이 길어질 수 있다면 작업 분할 또는 병렬 처리를 검토하세요. 실행 시간 모니터링은 필수입니다.

-- 평균 / 최대 실행 시간 확인
SELECT AVG(EXTRACT(SECOND FROM run_duration) +
           EXTRACT(MINUTE FROM run_duration) * 60) AS avg_seconds,
       MAX(EXTRACT(SECOND FROM run_duration) +
           EXTRACT(MINUTE FROM run_duration) * 60) AS max_seconds
FROM   user_scheduler_job_run_details
WHERE  job_name = 'JOB_TAX_PURCHASE_A10'
  AND  status = 'SUCCEEDED'
  AND  actual_start_date > SYSDATE - 30;

2. JOB_QUEUE_PROCESSES 파라미터 확인

이 값이 0이면 모든 스케줄러 작업이 중지됩니다.

SHOW PARAMETER job_queue_processes

기본값은 보통 1000입니다. 너무 낮게 설정되어 있으면 동시 실행 작업이 많을 때 대기가 발생합니다.

3. 권한 확인

PROC_TAX_PURCHASE_A10이 다른 스키마에 있다면 EXECUTE 권한이 필요하고, 작업을 만들 사용자는 CREATE JOB 시스템 권한이 있어야 합니다.

-- 권한 확인
SELECT * FROM user_sys_privs WHERE privilege LIKE '%JOB%';
SELECT * FROM user_tab_privs WHERE table_name = 'PROC_TAX_PURCHASE_A10';

4. RAC 환경에서 특정 노드 지정

특정 인스턴스에서만 실행하고 싶다면:

BEGIN
    DBMS_SCHEDULER.SET_ATTRIBUTE(
        name      => 'JOB_TAX_PURCHASE_A10',
        attribute => 'INSTANCE_ID',
        value     => 1
    );
END;
/

5. 작업 실행 중 DB 재시작 시 동작

작업이 실행 중인데 DB가 셧다운되면 해당 실행은 중단되고, 다음 스케줄 시각부터 정상 동작합니다. 중단된 실행은 자동 재시작되지 않습니다. 누락된 데이터가 있다면 수동 실행이 필요합니다.

 

마무리

DBMS_SCHEDULER로 동적 인자가 필요한 배치 작업을 등록할 때 핵심은 세 가지입니다.

1. PLSQL_BLOCK 타입 + job_action 안에 인자 평가 — 매 실행 시점에 SYSDATE가 새로 계산됩니다.
2. repeat_interval에 BYHOUR/BYMINUTE/BYSECOND 명시 — 정확한 시각에 실행하려면 모두 지정해야 합니다.
3. 등록 후 EVALUATE_CALENDAR_STRING으로 검증 — 예상과 다른 시각에 잡히는 사고를 미리 막습니다.

이 글의 예제는 매일 4회 실행되는 세금/구매 데이터 처리지만, 같은 패턴으로 매시간 통계 갱신, 매주 마감 작업, 매월 1일 정산 배치 등 어떤 주기 작업이라도 응용 가능합니다. 본인 환경의 실행 시각과 프로시저 이름만 바꾸면 됩니다.

비슷한 배치 작업을 운영하면서 만난 이슈나, 더 좋은 패턴이 있다면 댓글로 공유해 주세요.