[PL/SQL] DBMS_JOB

DBMS_JOB

잡을 등록하고 관리하는 데 사용되는 시스템 패키지이다.

우선 예제용 테이블을 하나 만든다.

CREATE TABLE CH15_JOB_TEST
(
  SEQ           NUMBER
, INSERT_DATE   DATE
);

그리고 이 테이블에 데이터를 INSERT 하는 프로시저를 만든다.

CREATE OR REPLACE PROCEDURE CH15_JOB_TEST_PROC
IS
    VN_NEXT_SEQ     NUMBER;
BEGIN
    SELECT NVL(MAX(SEQ), 0) + 1
      INTO VN_NEXT_SEQ
      FROM CH15_JOB_TEST;
      
    INSERT INTO CH15_JOB_TEST
    VALUES (VN_NEXT_SEQ, SYSDATE);
    
    COMMIT;
    
EXCEPTION WHEN OTHERS THEN
    ROLLBACK;
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

1. 잡 등록 : DBMS_JOB.SUBMIT

DBMS_JOB.SUBMIT
(
  JOB       OUT BINARY_INTEGER
, WHAT      IN  VARCHAR2
, NEXT_DATE IN  DATE            DEFAULT SYSDATE
, INTERVAL  IN  VARCHAR2        DEFAULT 'NULL'
, NO_PARSE  IN  BOOLEAN         DEFAULT FALSE
, INSTANCE  IN  BINARY_INTEGER  DEFAULT ANY_INSTANCE
, FORCE     IN  BOOLEAN         DEFAULT FALSE
);

- JOB : 잡 번호, 출력 변수로 자동으로 번호가 매겨진다.

- WHAT : 실행될 프로그램, 문자열 형태로 SQL이나 PL/SQL이 온다.

- NEXT_DATE : 잡이 실행될 다음 날짜

- INTERVAL : 잡의 실행 주기로, 문자열 형태의 값

- NO_PARSE : FALSE로 설정하면 오라클은 해당 잡과 연관된 프로시저를 파싱하고 TRUE로 설정하면 잡이 맨 처음 실행됬을 때만 파싱한다.

- INSTANCE : 잡을 등록할 때 이 잡을 실행시킬 수 있는 특정 인스턴스를 명시하는데, 디폴트 값 0은 어느 인스턴스나 실행하라는 의미이다. NULL이나 음수를 명시하면 ORA-23319 오류가 발생한다.

- FORCE : TRUE로 설정하면 INSTANCE 매개변수에서 설정한 인스턴스 번호 외의 양수인 다른 인스턴스가 해당 잡을 실행할 수 있다.

- 일주일에 한 번 : SYSDATE + 7

- 하루에 한 번 : SYSDATE + 1

- 1시간에 한 번 : SYSDATE + 1/24

- 매주 수요일 오후 11시 : NEXT_DAY(TRUNC(SYSDATE), '수요일') + 23/24

- 매월 마지막날 오후 6시 30분 : LAST_DAY(TRUNC(SYSDATE)) + 18/24 + 30/60/24

DECLARE
    V_JOB_NO    NUMBER;
BEGIN
    DBMS_JOB.SUBMIT
    (
      JOB       => V_JOB_NO
    , WHAT      => 'CH15_JOB_TEST_PROC;'
    , NEXT_DATE => SYSDATE
    , INTERVAL  => 'SYSDATE + 1/60/24'
    );
    
    COMMIT;
    
    DBMS_OUTPUT.PUT_LINE('V_JOB_NO : ' || V_JOB_NO);
END;

잠시 기다린 뒤, 예제 테이블의 데이터를 조회해보자.

SELECT SEQ
     , TO_CHAR(INSERT_DATE, 'YYYY-MM-DD HH24:MI:SS')
  FROM CH15_JOB_TEST;

1분마다 데이터가 INSERT 된 것을 확인할 수 있다.

현재 등록된 잡 목록을 조회하는 방법은 아래와 같다.

SELECT JOB
     , LAST_DATE
     , LAST_SEC
     , NEXT_DATE
     , NEXT_SEC
     , BROKEN
     , INTERVAL
     , FAILURES
     , WHAT
  FROM USER_JOBS;

주의할 사항

 WHAT 매개변수 맨 마지막에 반드시 세미콜론이 붙어야한다.

 DBMS_JOB.SUBMIT 프로시저 호출 이후에 COMMIT문을 실행해야한다.

 등록할 잡 번호는 시스템에서 자동 생성해주므로 DBMS_JOB.SUBMIT 프로시저를 호출할 때는 잡 번호를 받을 션수를 지정할 수 있도록 익명블록형태로 실행해야한다.

2. 잡 중지와 재실행 : DBMS_JOB.BROKEN

등록된 잡을 일시 중지 시키거나 중지시킨 잡을 재실행할 때 사용한다.

DBMS_JOB.BROKEN
(
  JOB       IN  BINARY_INTEGER
, BROKEN    IN  BOOLEAN
, NEXT_DATE IN  DATE            DEFAULT SYSDATE
);

- JOB : 잡 번호

- BROKEN : 잡을 중지할 때는 TRUE, 다시 실행할 때는 FALSE

- NEXT_DATE : 잡이 중지되거나 재실행될 날짜

등록된 잡을 중지시키고 확인해보자.

BEGIN
    DBMS_JOB.BROKEN(1, TRUE);
    COMMIT;
END;
 
SELECT JOB
     , LAST_DATE
     , LAST_SEC
     , NEXT_DATE
     , NEXT_SEC
     , BROKEN
     , INTERVAL
     , FAILURES
     , WHAT
  FROM USER_JOBS;

BROKEN 컬럼의 값이 N 에서 Y로 변한 것을 확인할 수 있다. 이 컬럼의 값이 Y 이면 잡이 중지된 상태이다.

3. 잡 속성 변경 : DBMS_JOB.CHANGE

DBMS_JOB.CHANGE
(
  JOB       OUT BINARY_INTEGER
, WHAT      IN  VARCHAR2
, NEXT_DATE IN  DATE            DEFAULT SYSDATE
, INTERVAL  IN  VARCHAR2        DEFAULT 'NULL'
, INSTANCE  IN  BINARY_INTEGER  DEFAULT ANY_INSTANCE
, FORCE     IN  BOOLEAN         DEFAULT FALSE
);

예제 테이블을 비우고 잡의 속성을 변경해보자.

TRUNCATE TABLE CH15_JOB_TEST;
 
BEGIN
    DBMS_JOB.CHANGE
    (
      JOB       => 1
    , WHAT      => 'CH15_JOB_TEST_PROC;'
    , NEXT_DATE => SYSDATE
    , INTERVAL  => 'SYSDATE + 3/60/24'
    );
    
    COMMIT;
END;
 
SELECT SEQ
     , TO_CHAR(INSERT_DATE, 'YYYY-MM-DD HH24:MI:SS')
  FROM CH15_JOB_TEST;

1분마다 동작하던 잡이 3분마다 동작했음을 확인할 수 있다.

4. 잡의 실행과 삭제 : DBMS_JOB.RUN DBMS_JOB.REMOVE

DBMS_JOB.RUN : 주기에 상관없이 강제로 실행하는 것

DBMS_JOB.RUN
(
  JOB   IN  BINARY_INTEGER
, FORCE IN  BOOLEAN         DEFAULT FALSE
)

DBMS_JOB.REMOVE : 등록된 잡을 제거하는 것

DBMS_JOB.REMOVE
(
  JOB   IN  BINARY_INTEGER
);

등록된 잡을 제거하고 확인해보자.

BEGIN
    DBMS_JOB.REMOVE(1);
END;
 
SELECT JOB
     , LAST_DATE
     , LAST_SEC
     , NEXT_DATE
     , NEXT_SEC
     , BROKEN
     , INTERVAL
     , FAILURES
     , WHAT
  FROM USER_JOBS;