DBMS_JOB

개요

DBMS_JOB은 오라클 호환 잡 스케줄링 패키지로, 프로시저 호출 또는 PL/SQL 블록을 일정에 따라 실행합니다.

내부적으로 o2scheduler 프레임워크를 사용하므로, 사용 전 o2scheduler의 사전 설정과 활성화가 요구됩니다.

DBMS_JOB에서 필요로 하는 추가 객체들은 dbms_job 스키마 내부에 생성되며, 메타 데이터와 실행 이력은 o2scheduler 스키마의 메타 테이블을 사용합니다.

---

제공 프로시져 일람

프로시저	설명
broken	Job의 상태를 broken 또는 not broken 으로 설정합니다.
change	Job의 정보를 수정합니다.
interval	Job의 interval(job이 실행되는 반복 주기 )를 수정합니다.
next_date	Job의 다음 수행시간을 설정합니다.
remove	Job을 제거합니다.
run	Job의 broken 상태 상관없이 강제로 지금 실행시킵니다.
submit	Job을 신규 생성합니다.
what	Job의 what(job이 수행해야하는 작업 정의)를 수정합니다.

---

활성화

o2scheduler

DBMS_JOB을 사용하기 전, 반드시 o2scheduler 를 설치 및 활성화하여야 합니다.

o2scheduler 설치 관련은 o2scheduler 참조서를 확인합니다.

dbms_job

o2scheduler 활성화 후 DBMS_JOB을 활성화합니다.

CREATE EXTENSION dbms_job;
# 또는 CREATE EXTENSION dbms_job CASCADE; 를 통해 o2scheduler를 동시에 같이 설치합니다.

• 설치 결과

  • 스키마 dbms_job 생성

  • 테이블 dbms_job.broken_job 생성(job의 broken 여부 관리하는 dbms_job 전용 메타테이블)

  • 프로시저: dbms_job.submit/remove/what/run/next_date/interval/change/broken

요약

DBMS_JOB에는 job 기능을 수행하기 위해 아래의 개념을 정의 및 사용합니다.

• what

  • job이 수행할 작업을 정의하는 개념이며, 아래같이 2가지 유형으로 존재할 수 있습니다.

    • 프로시저 이름 형식 what: e.g., my_proc

    • PL/SQL 블록 형식 what: e.g., BEGIN my_proc(); END;

• interval

  • job이 수행될 반복주기를 정의하는 개념입니다.

  • 오라클의 SYSDATE, SYSTIMESTAMP 키워드를 사용할 수 있으며, 해당 키워드 뒤에 붙일 수 있는 +1 같은 오라클 형식의 산수는 PostgreSQL가 이해가능한 PostgreSQL 문법으로 변환합니다.

    • 예) SYSDATE+1 입력 시 → now() + interval ‘1’ day 로 내부에서 변환하여 interval 식으로 사용합니다.

  • 지원되는 오라클 문법 및 PostgreSQL 형식으로 변환되는 키워드는 아래와 같습니다.

    • SYSDATE → now()

    • SYSTIMESTAMP → clock_timestamp()

    • +1 → + interval ‘1 day’

    • +1/24 → + interval ‘1 hour’

    • +1/1440 → + interval ‘1 minute’

    • +1/86400 → + interval ‘1 second’

Job 실행 이력 및 정보

DBMS_JOB은 내부 동작에서 o2scheduler를 의존하고 있으며, 각 실행이력과 생성/수정되는 job 정보는 다음처럼 저장됩니다.

• 이력: o2scheduler.job_run_details

• 다음 실행 예정 시각: o2scheduler.job

Broken

DBMS_JOB은 job 수행 실패 시 해당 job의 실패 카운트를 누적합니다.

해당 job이 총 10회 이상 실패 시 해당 job을 broken 상태로 자동 갱신하여 더이상 job이 반복 실행되지 않도록 비활성화합니다.

SUBMIT

개요

새로운 작업을 등록하고 스케줄링합니다. 저장된 프로시저를 호출하거나 PL/SQL 블록을 지정된 시각에 실행하도록 예약합니다.

프로토타입

CALL dbms_job.submit(
  OUT job integer,
  IN what text,
  IN next_date timestamptz DEFAULT CURRENT_TIMESTAMP,
  IN "interval" text DEFAULT NULL,
  IN no_parse boolean DEFAULT false
)

파라미터

파라미터	타입	기본값	설명
job	integer	-	OUT 파라미터이다. 생성된 작업의 고유 ID를 반환합니다.
what	text	-	실행할 작업 내용이다; 실행할 프로시저 이름 또는 PL/SQL 블록 문자열을 입력합니다. what 스트링이 공백 기준 1개의 단어로 구성되어 있으면 실행할 프로시져의 이름으로, 2개 이상의 단어로 구성되어있으면 PL/SQL 블록으로 what을 인식합니다.
next_date	timestamptz	CURRENT_TIMESTAMP	다음 실행 시각입니다; NULL입력 시 내부에서 now로 설정합니다.
interval	text	NULL	반복 주기 표현식입니다.; NULL이면 일회성 작업을 나타냅니다.
no_parse	boolean	false	구문 검증 지연 여부입니다. (현재 미사용; 오라클 호환을 위해 존재합니다.)

예제

-- 1회 실행(즉시); my_proc 이라는 이름의 프로시져를 지금 당장 실행하는 job을 등록한다.
CALL dbms_job.submit(NULL, 'my_proc');

-- 1시간 주기 반복; my_proc 이라는 이름의 프로시져를 1시간 주기로 반복 실행하는 job을 등록 및 바로 실행한다.
CALL dbms_job.submit(NULL, 'my_proc', "interval" => 'SYSDATE+1/24');

-- 1분 후 첫 실행하고, 이후 매분 반복하는 PL/SQL job을 등록한다.
CALL dbms_job.submit(NULL, 'BEGIN my_proc(); END;',
                     next_date => now() + interval '1 minute',
                     "interval" => 'now() + interval ''1'' minute');

REMOVE

개요

지정된 작업을 논리적으로 삭제(soft delete)합니다.

작업이 완전히 제거되지 않고 비활성화 상태가 되어 이후 스케줄링되지 않습니다.

• 어떤 job이었는지 history 확인을 위한 의도로써 soft delete 만 수행합니다.

• hard delete를 위해서는 o2scheduler.job 테이블에 해당 job을 직접 delete 해야하며, 이때 다른 job을 지우지 않도록 유의해야 합니다.

프로토타입

CALL dbms_job.remove(IN job integer)

파라미터

파라미터	타입	설명
job	integer	삭제할 작업의 ID입니다.

예제

-- 작업 ID 123 삭제
CALL dbms_job.remove(123);

WHAT

개요

기존 작업의 실행 내용을 변경합니다.

프로시저명 또는 PL/SQL 블록을 새로운 내용으로 교체합니다.

프로토타입

CALL dbms_job.what(IN job integer, IN what text)

파라미터

파라미터	타입	설명
job	integer	변경할 작업의 ID입니다.
what	text	새로운 실행 내용입니다. NULL 또는 빈 문자열은 쓸 수 없습니다.

예제

-- 작업 ID 123의 실행 내용을 다른 프로시저로 변경
CALL dbms_job.what(123, 'my_other_proc');

-- PL/SQL 블록으로 변경
CALL dbms_job.what(123, 'BEGIN my_proc(); my_other_proc(); END;');

RUN

개요

작업의 상태와 관계없이 즉시 실행합니다.

broken 상태의 작업도 강제로 실행합니다.

run한 job은 내부 동작에 의해 다음 실행시각이 now로 갱신되어 실행됩니다.

프로토타입

CALL dbms_job.run(IN job integer)

파라미터

파라미터	타입	설명
job	integer	실행할 작업의 ID입니다.

예제

-- 작업 ID 123 즉시 실행
CALL dbms_job.run(123);

NEXT_DATE

개요

작업의 다음 실행 시각을 변경합니다.

next_date에 NULL 지정 시 스케줄링을 비활성화합니다.

프로토타입

CALL dbms_job.next_date(IN job integer, IN next_date timestamptz)

파라미터

파라미터	타입	설명
job	integer	변경할 작업의 ID입니다.
next_date	timestamptz	새로운 다음 실행 시각입니다. NULL이면 스케줄링 비활성화합니다.

예제

-- 작업 ID 123을 1시간 후에 실행하도록 변경
CALL dbms_job.next_date(123, now() + interval '1 hour');

-- 작업 ID 123의 스케줄링 비활성화
CALL dbms_job.next_date(123, NULL);

INTERVAL

개요

작업의 반복 주기를 변경합니다.

interval에 NULL 지정 시 일회성 작업으로 변경됩니다.

프로토타입

CALL dbms_job.interval(IN job integer, IN "interval" text)

파라미터

파라미터	타입	설명
job	integer	변경할 작업의 ID입니다.
interval	text	새로운 반복 주기 표현식입니다. NULL이면 일회성 작업으로 변경합니다.

예제

-- 작업 ID 123을 매일 실행하도록 변경
CALL dbms_job.interval(123, 'SYSDATE+1');

-- 작업 ID 123을 일회성 작업으로 변경
CALL dbms_job.interval(123, NULL);

CHANGE

개요

작업의 여러 속성을 한 번에 변경합니다.

NULL로 지정된 파라미터는 기존 값을 유지합니다.

프로토타입

CALL dbms_job.change(
  IN job integer,
  IN what text,
  IN next_date timestamptz,
  IN "interval" text,
  IN instance integer DEFAULT 0,
  IN "force" boolean DEFAULT false
)

파라미터

파라미터	타입	기본값	설명
job	integer	-	변경할 작업의 ID입니다
what	text	-	새로운 실행 내용입니다. NULL이면 기존 값 유지합니다.
next_date	timestamptz	-	새로운 다음 실행 시각입니다. NULL이면 기존 값 유지합니다.
interval	text	-	새로운 반복 주기입니다. NULL이면 기존 값 유지합니다.
instance	integer	0	인스턴스 번호입니다.(현재 미사용입니다.; 오라클 호환을 위해 존재합니다.)
force	boolean	false	강제 실행 여부입니다.(현재 미사용입니다.; 오라클 호환을 위해 존재합니다.)

예제

-- 작업 ID 123의 실행 내용과 주기를 모두 변경
CALL dbms_job.change(123, 'new_proc', now() + interval '2 hours', 'SYSDATE+1/12');

-- 작업 ID 123의 실행 시각만 변경
CALL dbms_job.change(123, NULL, now() + interval '30 minutes', NULL);

BROKEN

개요

job을 broken 또는 not broken 상태로 갱신합니다.

broken상태의 job은 비활성화되어 실행되지 않으며, DBMS_JOB.RUN 프로시저를 통해 강제 실행할 수 있습니다.

프로토타입

CALL dbms_job.broken(
  IN job integer,
  IN broken boolean,
  IN next_date timestamptz DEFAULT current_timestamp
)

파라미터

파라미터	타입	기본값	설명
job	integer	-	변경할 작업의 ID입니다.
broken	boolean	-	broken 상태 설정 여부입니다.
next_date	timestamptz	current_timestamp	복구 시 다음 실행 시각입니다.

예제

-- 작업 ID 123을 깨진 상태로 설정
CALL dbms_job.broken(123, true);

-- 작업 ID 123을 복구하고 1시간 후에 실행하도록 설정
CALL dbms_job.broken(123, false, now() + interval '1 hour');

What 구분 규칙

job이 수행해야 할 what이 1개의 단어로만 구성된 스트링이면 프로시저 이름으로, 2개 이상의 단어로 구성되어있으면 PL/SQL 블록으로 what을 인식합니다.

예시

• my_proc → stored_procedure

• BEGIN my_proc(); END; → plsql_block

---

구성 파라미터(GUC)

dbms_job.failure_threshold

• 설명: 연속 실행 실패 허용 최대치. 초과 시 잡을 비활성화(broken 상태)로 자동 갱신합니다.

• 기본값: 10, 최소 3, 최대 20

• 적용 범위: SIGHUP(설정 변경 후 pg_ctl reload 또는 SIGHUP 필요), superuser만 변경 가능합니다

예

# postgresql.conf
# ...
dbms_job.failure_threshold = 10

job 실행 실패 시, dbms_job.broken_job 테이블에서 관리하는 해당 job의 failed_count가 증가되고, 임계치(dbms_job.failure_threshold) 초과 시 해당 job이 비활성화됩니다.

---

운영 가이드

상태/이력 확인

-- 최근 실행 이력
SELECT * FROM o2scheduler.job_run_details ORDER BY id DESC LIMIT 20;

-- 등록된 잡
SELECT * FROM o2scheduler.job ORDER BY id DESC LIMIT 20;

권장 설정

• BGW가 충분히 동작할 수 있도록 max_worker_processes를 워크로드 기준으로 튜닝해야 합니다.

• 스케줄 표현식은 가능하면 의미가 분명한 형태 사용해야 합니다.(예: SYSDATE+1/24, now() + interval '1 minute')

트랜잭션 유의사항

• 제출 직후 테이블 상태만 확인하는 경우 하나의 트랜잭션에서 ROLLBACK해도 무방하지만, 실제 실행/이력 확인 테스트는 COMMIT 이후에 이루어져야 BGW가 잡을 집계할 수 있습니다.

트러블슈팅 체크리스트

• CREATE EXTENSION o2scheduler;가 해당 DB에 적용되어 있는가?

• shared_preload_libraries에 o2scheduler가 포함되고 서버를 재기동했는가?

• max_worker_processes가 부족하지 않은가?

• dbms_job.failure_threshold 값이 환경에 적절한가?

• o2scheduler.job_run_details에 실패 이력이 기록되는가? 실패 메시지를 확인해 표현식/프로시저 존재 여부 점검

부록: 스키마 개요

dbms_job.broken_job(job_id int primary key, failed_count int, updated_at timestamptz)

메타(참고 전용)

• o2scheduler.job(id, username, command_expr, command_type, schedule_expr, schedule_type, schedule_time, created_at, updated_at, deleted_at)

• o2scheduler.job_run_details(id, job_id, worker_pid, username, status, message, start_time, end_time, schedule_time)