Oracle Job Process 스케줄

1. JOB 등록 방법

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);

EXEC DBMS_JOB.SUBMIT(:V_JOBNO
, '/* 소속 직책자 자동변경 */
UPDATE T0201001 X
SET OFFI_EMP_NO
= (SELECT EMP_NO
FROM T0401001
WHERE OFFI_RES_CD = ''003600''
AND RETI_DATE IS NULL
AND CUR_ASGN_CD = X.ASGN_CD)
WHERE ASGN_CD
IN (SELECT CUR_ASGN_CD
FROM T0401001 A
WHERE A.OFFI_RES_CD = ''003600''
AND A.RETI_DATE IS NULL);'
, '01/01/4000 00:00:00' -- NEXTDATE
, 'TRUNC(SYSDATE+1)'); -- INTERVAL

INTERVAL 은

매일 'SYSDATE + 1'
일주일에한번 'SYSDATE + 7'
1시간에 한번 'SYSDATE + 1/24'
10분에 한번 'SYSDATE + 10/1440'
30초에 한번 'SYSDATE + 30/86400'

----------------------------------------------------------------------

2. 유지관리

(1) JOB 변경방법

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

SELECT * FROM USER_JOBS;

EXEC DBMS_JOB.CHANGE(37, NULL, NULL, 'SYSDATE + 3');

(2) JOB 주기만 변경

DBMS_JOB.INTERVAL(JOB IN BINARY_INTEGER
, INTERVAL IN VARCHAR2);

SELECT * FROM USER_JOBS;

EXEC DBMS_JOB.INTERVAL(37, 'TRUNC(SYSDATE) + 24/24');

(3) JOB 실행(NOW)

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

SELECT * FROM USER_JOBS;

EXEC DBMS_JOB.RUN(37);

(4) JOB EXPORT

SELECT * FROM USER_JOBS;

DECLARE
CALLSTR VARCHAR2(500);
BEGIN
DBMS_JOB.USER_EXPORT(84, CALLSTR);
DBMS_OUTPUT.PUT_LINE(CALLSTR);
END;

(5) JOB 삭제

SELECT * FROM USER_JOBS;

EXEC DBMS_JOB.REMOVE(37);

출처 : Tong - jtfafa님의 Oracle통