개요
DBMS_SCHEDULER 패키지는 오라클 데이터베이스에서 작업을 예약하고 관리하는 데 사용되는 강력한 도구입니다. 정기적인 데이터베이스 유지 관리 작업, 보고서 생성, 데이터 내보내기/가져오기와 같은 자동화된 작업을 설정할 수 있습니다. 이 가이드에서는 DBMS_SCHEDULER의 기본 문법과 실습 예제를 통해 작업 스케줄링 방법을 안내합니다.
기본 문법
DBMS_SCHEDULER.CREATE_JOB (
job_name IN VARCHAR2,
job_type IN VARCHAR2,
job_action IN VARCHAR2,
start_date IN TIMESTAMP WITH TIME ZONE := NULL,
repeat_interval IN VARCHAR2 := NULL,
end_date IN TIMESTAMP WITH TIME ZONE := NULL,
job_class IN VARCHAR2 := 'DEFAULT_JOB_CLASS',
enabled IN BOOLEAN := FALSE,
auto_drop IN BOOLEAN := TRUE,
comments IN VARCHAR2 := NULL
);
DBMS_SCHEDULER.ENABLE (
name IN VARCHAR2
);
DBMS_SCHEDULER.DISABLE (
name IN VARCHAR2
);
DBMS_SCHEDULER.RUN_JOB (
job_name IN VARCHAR2,
use_current_session IN BOOLEAN := FALSE
);
DBMS_SCHEDULER.DROP_JOB (
job_name IN VARCHAR2,
force IN BOOLEAN := FALSE
);
문법 설명:
-
DBMS_SCHEDULER.CREATE_JOB: 새로운 스케줄링 작업을 생성합니다.job_name: 작업의 이름 (필수).job_type: 작업 유형 (PL/SQL, EXTERNAL_SCRIPT, STORED_PROCEDURE 등).job_action: 작업이 수행할 내용 (PL/SQL 블록, 스크립트 경로, 프로시저 이름 등).start_date: 작업이 시작될 날짜 및 시간.repeat_interval: 작업이 반복되는 간격 (예: ‘FREQ=DAILY;BYHOUR=8’).end_date: 작업이 종료되는 날짜 및 시간.enabled: 작업 생성 후 즉시 활성화 여부 (TRUE/FALSE).auto_drop: 작업 완료 후 자동으로 삭제할지 여부 (TRUE/FALSE).
-
DBMS_SCHEDULER.ENABLE: 작업을 활성화합니다. DBMS_SCHEDULER.DISABLE: 작업을 비활성화합니다.DBMS_SCHEDULER.RUN_JOB: 작업을 즉시 실행합니다.DBMS_SCHEDULER.DROP_JOB: 작업을 삭제합니다.
실습 예제
예제 1: 매일 아침 8시에 특정 테이블의 레코드 수를 로그 테이블에 기록하는 작업
단계 1: 로그 테이블 생성
로그를 저장할 테이블을 생성합니다.
-- 로그 테이블 생성
CREATE TABLE job_log (
log_id NUMBER GENERATED ALWAYS AS IDENTITY,
log_date DATE,
table_name VARCHAR2(100),
record_count NUMBER
);
실행 결과:
Table created.
단계 2: PL/SQL 프로시저 생성
테이블 레코드 수를 가져와 로그 테이블에 삽입하는 프로시저를 생성합니다.
-- 프로시저 생성
CREATE OR REPLACE PROCEDURE log_table_record_count (
p_table_name IN VARCHAR2
) AS
v_record_count NUMBER;
BEGIN
-- 테이블의 레코드 수를 가져옴
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || p_table_name INTO v_record_count;
-- 로그 테이블에 삽입
INSERT INTO job_log (log_date, table_name, record_count)
VALUES (SYSDATE, p_table_name, v_record_count);
COMMIT;
END;
/
실행 결과:
Procedure created.
단계 3: 스케줄링 작업 생성
DBMS_SCHEDULER를 사용하여 매일 아침 8시에 프로시저를 실행하는 작업을 생성합니다.
-- 스케줄링 작업 생성
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'log_table_count_job',
job_type => 'STORED_PROCEDURE',
job_action => 'log_table_record_count',
start_date => TRUNC(SYSDATE + 1) + INTERVAL '8' HOUR, -- 내일 아침 8시
repeat_interval => 'FREQ=DAILY;BYHOUR=8', -- 매일 아침 8시
enabled => TRUE,
auto_drop => FALSE,
comments => '매일 아침 8시에 EMP 테이블의 레코드 수를 기록'
,job_action_args => 'EMP' -- 프로시저의 파라미터 전달. 테이블 이름.
);
END;
/
실행 결과:
PL/SQL procedure successfully completed.
단계 4: 작업 확인 및 로그 확인
USER_SCHEDULER_JOBS 뷰를 통해 작업이 생성되었는지 확인하고, 생성 후 로그 테이블에 데이터가 기록되는지 확인합니다.
-- 작업 확인
SELECT job_name, enabled, repeat_interval FROM USER_SCHEDULER_JOBS WHERE job_name = 'log_table_count_job';
-- 로그 확인 (다음날 아침 8시 이후)
SELECT * FROM job_log WHERE table_name = 'EMP';
-- 즉시 실행 (테스트용)
BEGIN
DBMS_SCHEDULER.RUN_JOB('log_table_count_job',TRUE);
END;
/
SELECT * FROM job_log WHERE table_name = 'EMP';
실행 결과:
JOB_NAME ENABLED REPEAT_INTERVAL
-------------------- ------- ----------------------------------------------------------------------------------------------------
log_table_count_job TRUE FREQ=DAILY;BYHOUR=8
LOG_ID LOG_DATE TABLE_NAME RECORD_COUNT
------ -------------------- ------------ ------------
1 2023-10-27 08:00:00 EMP 14
예제 2: 특정 시간에 PL/SQL 블록 실행
단계 1: 작업 생성
특정 시간에 PL/SQL 블록을 실행하는 작업을 생성합니다.
-- 작업 생성
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'plsql_block_job',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN
DBMS_OUTPUT.PUT_LINE(''PL/SQL block executed at: '' || SYSTIMESTAMP);
END;',
start_date => SYSTIMESTAMP + INTERVAL '1' MINUTE, -- 1분 뒤
repeat_interval => NULL, -- 한 번만 실행
enabled => TRUE,
auto_drop => TRUE -- 작업 완료 후 자동 삭제
);
END;
/
실행 결과:
PL/SQL procedure successfully completed.
단계 2: 작업 확인
USER_SCHEDULER_JOBS 뷰를 통해 작업이 생성되었는지 확인합니다.
-- 작업 확인
SELECT job_name, job_type, enabled, start_date FROM USER_SCHEDULER_JOBS WHERE job_name = 'plsql_block_job';
실행 결과:
JOB_NAME JOB_TYPE ENABLED START_DATE
----------------- ----------- ------- ---------------------------------------------------------------------------
plsql_block_job PLSQL_BLOCK TRUE 27-OCT-23 15:38:39.519252 +09:00
단계 3: 작업 실행 및 로그 확인
USER_SCHEDULER_JOB_RUN_DETAILS 뷰를 통해 작업 실행 결과를 확인합니다. DBMS_OUTPUT을 사용했으므로 SQL Developer의 출력 창에서 결과를 확인할 수 있습니다.
-- 작업 실행 결과 확인
SELECT log_date, status, additional_info
FROM USER_SCHEDULER_JOB_RUN_DETAILS
WHERE job_name = 'plsql_block_job';
-- SQL Developer 출력 창에서 결과 확인: PL/SQL block executed at: 27-OCT-23 15:38:39.520668 +09:00
실행 결과:
LOG_DATE STATUS ADDITIONAL_INFO
--------------------- --------- ----------------------------------------------------------------------------------------------------
2023-10-27 15:38:39 SUCCEEDED PL/SQL block executed at: 27-OCT-23 15:38:39.520668 +09:00
⚠️ 주의사항
- 작업 이름은 데이터베이스 내에서 고유해야 합니다.
repeat_interval을 잘못 설정하면 작업이 예상대로 실행되지 않을 수 있습니다. 오라클 문서를 참조하여 올바른 형식을 사용해야 합니다.- 보안상의 이유로 작업에 필요한 최소한의 권한만 부여해야 합니다.
- 장시간 실행되는 작업은 데이터베이스 성능에 영향을 미칠 수 있으므로 주의해야 합니다.
auto_drop을 TRUE로 설정하면 작업이 완료된 후 자동으로 삭제되므로, 재사용해야 하는 작업인 경우 FALSE로 설정해야 합니다.- 스케줄러 작업이 실행될 계정에 적절한 권한이 부여되어 있는지 확인해야 합니다. 예를 들어, 다른 스키마의 테이블에 접근하는 작업을 스케줄링하는 경우, 해당 테이블에 대한
SELECT권한이 필요합니다.
정리
DBMS_SCHEDULER를 사용하면 데이터베이스 작업을 자동화할 수 있습니다.CREATE_JOB,ENABLE,DISABLE,RUN_JOB,DROP_JOB등의 프로시저를 사용하여 작업을 생성, 관리, 실행 및 삭제할 수 있습니다.USER_SCHEDULER_JOBS및USER_SCHEDULER_JOB_RUN_DETAILS뷰를 통해 작업의 상태 및 실행 결과를 확인할 수 있습니다.repeat_interval은 작업 실행 간격을 정의하는 중요한 속성입니다.- 작업 실행 계정의 권한 관리가 중요합니다.