오라클 잡 관리(Oracle job manage)

 

오라클 잡 관리(Oracle job manage)

   

.주기적으로 수행되어지는 JOB을 JOB QUEUE를 사용하여 스케줄링 할 수 있습니다..

 JOB을 스케줄링 하기 위해서 ORACLE의 DBMS_JOB 패키지를 이용합니다..

 JOB QUEUE PROCESS가 JOB QUEUE 안의 잡을 수행합니다..

 JOB으로 등록될 수 있는 것은 PL/SQL 프로시저 또는 패키지가 되며

 예를 들어소스 디비의 테이블들에서 타겟 테이블로 데이터를 적재하는 프로시저를 생성했는데

 1분단위로 데이터를 타겟 테이블로 적재를 해야 할 때 DBMS_JOBS에 등록하여

 스케줄링 할 수 있습니다.

   

   

   

.JOB_QUEUE_PROCESSES 파라미터가 이와 관련된 초기화 파라미터로 0으로 설정되면

 JOB QUEUE PROCESS는 시작되지 않으며 JOB QUEUE의 어느 잡도 수행되지 않습니다..

 JOB_QUEUE_PROCESSES 파라미터의 MAX값이 설정되어야 오라클 인스턴스 위에서

 동시에 잡을 수행할 수 있다. 설정할 수 있는 최고 값은 1000입니다..

 JOB_QUEUE_PROCESSES=60 과 같이 설정할 수 있습니다..

   

   

   

.등록되거나 수행되는 잡에 대해서는 DBA_JOBS 또는 USER_JOBS 딕셔너리 뷰를 통해

 확인 할 수 있다.

   

.JOB_QUEUE_PROCESSES 는 다이나믹 하게 DB를 SHUTDOWN하지 않고 ALTER SYSTEM

 명령을 이용해서 설정할 수 있습니다.

 ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 20;

   

   

   

.JOB QUEUE안의 JOB을 스케줄링 하기 위해서는 DBMS_JOBS패키지를 사용할 수 있으며

 JOB_QUEUE를 사용하기 위해 관련된 DB 권한은 없다.

   

   

   

.다음은 DBMS_JOBS의 패키지를 사용하기 위한 패키지의 프로시져들입니다.

   

 SUBMIT - 잡큐의 잡을 등록합니다.

  REMOVE - 잡큐의 잡을 제거합니다.

  CHANGE - 잡큐의 잡을 변경합니다.

  NEXT_DATE - 잡의 다음 수행시간을 변경합니다.

  INTERVAL - 잡 수행 주기를 변경합니다.

  WHAT - 잡으로 등록된 프로시저 또는 패키지를 변경합니다.

  RUN - 잡을 수동으로 강제로 수행합니다.

   

   

 .새 JOB을 JOB QUEUE에 등록하기 위해 사용되는 파라미터로 DBMS_JOB.SUBMIT()에 들어가느

  파라미터 입니다.

   

  JOB - OUTPUT 파라미터로 생성한 잡에 의해 할당되는 식별자 입니다.

  WHAT - JOB QUEUE에 등록되는 PL/SQL 프로시저 또는 패키지 입니다.

  NEXT_DATE - 잡이 수행되는 다음 시간입니다.

  INTERVAL - 잡이 수행되는 주기로 초 단위까지 지정 가능합니다.

    

  JOB_QUEUE에 등록하는 예제입니다.

 VARIABLE jobno NUMBER

 BEGIN

   DBMS_JOB.SUBMIT(:jobno,

                   'SP_IN_EMP_SAL;',

                   SYSDATE,

                   'SYSDATE + 1');

   COMMIT;

 END;

 /

   

 위의 PL/SQL문을 SQL PLUS에서 수행합니다.

  첫번째 파라미터가 JOB NUMBER가 부여되는 부분이고

  두번째 파라미터가 WHAT으로 SP_IN_EMP_SAL이라는 프로시저를 등록했습니다.

  세번째 파라미터가 NEXT_DATE이며 4번째 파라미터가 잡 수행 주기로 하루에 한번씩

  수행하라는 의미입니다.

   

   

  DBMS_JOB을 이용하면 특정시간, 특정요일, 특정일, 30초단위, 매분 정각, 매시정각

  등 다양하게  잡을 스케줄링 하는 것이 가능합니다.

  ETL 수행 할 때도 유용하게 사용할 수 있습니다.

   

   

   

   

잡 수행 간격 조정의 예

 .SYSDATE+ 7  :  7일에 한번씩 잡이 수행됩니다.

 .SYSDATE+1/24 : 1시간에 한번씩 잡이 수행됩니다.

 .SYSDATE+30/86400 : 30초에 한번씩 잡이 수행됩니다.

 .최초 잡 수행시간이 14:02분일 경우 매시 14:02분에 잡을 수행해야 될 경우

   =>trunc(SYSDATE,'MI')+1/24 

 .최조 잡 수행시간이 06시 이고 매 8시간마다 정각에 잡이 수행되어야 될 경우

   =>trunc(SYSDATE,'MI')+8/24

 .매주 월요일 정각 3시에 잡이 수행되어야 할 경우

   =>NEXT_DAY(TRUNC(SYSDATE),'MONDAY')+15/25

 .각 분기마다 첫번째 월요일에 잡이 수행되어야 될 경우

   =>NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE,'Q'),3),'MONDAY')

   

주의> dbms_job으로 잡을 스케줄링하게 될 경우 예를 들어 잡 최초 수행시간이 22일 14:00시이고

         매시 정각에 잡이 수행되어야 할 경우 SYSDATE+1/24로 간격을 주게 되면 매 정시에 도는 것이

         아니라 잡 수행 시간에 따라 약간씩 늦어지게 되어 14:00:04 => 15:00:07 => 16:00:10 이런식으로

         잡 수행시간이 잡히게 됩니다. 따라서 정각에 수행되게 하려면 trunc함수를 이용해서 무조건 분에서

         잘라내여 00으로 만들어 준다. trunc(SYSDATE,'MI')

   

   

   

잡의 삭제

 BEGIN

 DBMS_JOB.REMOVE(14443);

 END;

 /

 14443은 잡 번호 이다. USER_JOBS 데이터 딕셔너리 뷰를 보면 잡 번호를 알 수 있습니다.

   

   

   

잡의 변경

 BEGIN

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

 END;

 /

   

   

잡으로 등록된 프로시저/패키지 변경

BEGIN

 DBMS_JOB.WHAT(14144,

     'SP_IN_EMP_SAL;');

END;

/

   

   

잡 다음 수행시간 변경

BEGIN

 DBMS_JOB.NEXT_DATE(14144, TRUNC(SYSDATE,'MI') + 4);

END;

/

   

   

   

   

잡 수행 간격 변경

BEGIN

 DBMS_JOB.INTERVAL(14144, TRUNC(SYSDATE,'MI')+30/1440);

END;

/

   

   

   

   

잡 수행 정지

  잡이 BROKEN되면 잡은 수행되지 않으며 강제로 수행 할 시에는 DBMS_JOB.RUN()을 통해  수행합니다..

BEGIN

 DBMS_JOB.BROKEN(14144, TRUE);

END;

/

   

   

   

   

잡큐 정보 VIEWING

 DBA_JOBS, USER_JOBS, ALL_JOBS를 이용합니다.

   

   

SELECT JOB, NEXT_DATE, NEXT_SEC, FAILURES, BROKEN

FROM DBA_JOBS;

   

JOB    NEXT_DATE  NEXT_SEC  FAILURES   B

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

9125    01-JUN-01   00:00:00     4       N

14144   24-OCT-01   16:35:35    0       N

9127    01-JUN-01   00:00:00    16       Y

3 rows selected.

   

   

   

   

DBMS_JOB의 활용예제를 주기적으로 소스테이블에서 타켓 테이블로

   

적재할 시 어떻게 사용할 수 있는지 간단하게 예를 만들었습니다

   

   

   

--수행될 잡의 목록이 들어갈 테이블

CREATE TABLE JOB_LIST

(JOB_ID VARCHAR2(2),

 JOB_TYPE VARCHAR2(1),

 JOB_NAME VARCHAR2(30),

 JOB_EXEC_HOUR VARCHAR(2),

 JOB_PARENTID VARCHAR2(2),

 CONSTRAINTS JOB_LIST_PK PRIMARY KEY(JOB_ID)

 USING INDEX

 TABLESPACE CYS_INDEX

 PCTFREE 0

 STORAGE(INITIAL 32K NEXT 32K PCTINCREASE 0)

 NOLOGGING)

 TABLESPACE CYS_DATA

 PCTFREE 0

 STORAGE(INITIAL 128K NEXT 128K PCTINCREASE 0);

 /

   

   

CREATE UNIQUE INDEX JOB_LIST_IDX01

ON JOB_LIST(JOB_NAME,JOB_EXEC_HOUR)

TABLESPACE CYS_INDEX

PCTFREE 0

STORAGE(INITIAL 64K NEXT 64K PCTINCREASE 0);

/

   

   

-잡 수행 히스토리 테이블로 하루에 한번씩 JOB_LIST 테이블에서 LOG테이블로 JOB_LIST가 복사된다.

CREATE TABLE JOB_LOG

(JOB_ID VARCHAR2(2),

 JOB_EXEC_DATE VARCHAR2(8),

 JOB_START_TIME DATE,

 JOB_END_TIME DATE,

 JOB_DATASTART_TIME DATE,

 JOB_DATAEND_TIME DATE,

 JOB_STATUS VARCHAR2(1),

 JOB_ERR_MSG VARCHAR2(100),

 CONSTRAINTS JOB_LOG_PK PRIMARY KEY(JOB_ID,JOB_EXEC_DATE)

 USING INDEX

 TABLESPACE CYS_INDEX

 PCTFREE 0

 STORAGE(INITIAL 128K NEXT 128K PCTINCREASE 0)

 NOLOGGING,

 CONSTRAINTS JOB_LIST_FK FOREIGN KEY(JOB_ID)

 REFERENCES JOB_LIST(JOB_ID))

 TABLESPACE CYS_DATA

 STORAGE(INITIAL 1M NEXT 1M PCTINCREASE 0)

   

   

--JOB_ID를 부여하기 위한 시퀀스

CREATE SEQUENCE JOB_NUM

START WITH 1

INCREMENT BY 1

   

   

--하루에 한번 DBMS_JOB을 이용하여 JOB_LIST의 JOB들을 JOB_LOG에 INSERT하기 위한 프로시져

--다음날 수행할 JOB을 LOG로 넣는다.

CREATE OR REPLACE PROCEDURE

SP_IN_JOB_LOG(V_INDATE IN VARCHAR2 DEFAULT NULL)

AS

BEGIN

  INSERT INTO JOB_LOG

  SELECT JOB_ID,

    NVL(V_INDATE,TO_CHAR(SYSDATE+1,'YYYYMMDD')),

   NULL,

   NULL,

   NULL,

   NULL,

   'N',

   NULL

  FROM JOB_LIST;

   

  COMMIT;

END SP_IN_JOB_LOG;

   

   

   

--SP_IN_JOB_LOG 프로시저를 DBMS_JOB에 등록한다.

DECLARE

    JOB_NUMBER NUMBER;

BEGIN

  DBMS_JOB.SUBMIT(JOB_NUMBER,  --JOB번호

        'SP_IN_JOB_LOG;',  --프로시저명

         TO_DATE('20050208180000','YYYYMMDDHH24MISS'),  --NEXT_DATE

        'TRUNC(SYSDATE,''MI'')+1');  --잡 수행 간격(매일 정각 6시)

END;

   

   

--JOB_LIST에 수행할 프로시져(JOB)을 등록

--DBA_USERS테이블을 이용해서 24개의 로우를 만들어 낸 후 카테시안 프러덕을 이용

INSERT INTO JOB_LIST

SELECT JOB_NUM.NEXTVAL JOB_ID,

    JOB_TYPE,

    JOB_NAME,

    B.CNT JOB_EXEC_HOUR,

    NULL

FROM(

  SELECT NULL JOB_ID,

      'F' JOB_TYPE,

      'SP_IN_F_SALE_SUM' JOB_NAME,

      NULL JOB_EXEC_HOUR,

      NULL

  FROM DUAL) A,

 ( SELECT LPAD(ROWNUM-1,2,'0') CNT FROM DBA_TABLES

     WHERE ROWNUM<25) B

   

COMMIT;

   

--JOB_LIST를 JOB_LOG로 INSERT(현재 SP_IN_JOB_LOG 가 다음일을 INSERT하도록 되어 있으므로 해당일을 넣어줌)

EXEC SP_IN_JOB_LOG('20050208');

   

   

--제대로 들어갔는지 확인

SELECT * FROM JOB_LOG;

   

   

--SOURCE 테이블을 시간단위로 섬머리 해서 TARGET 테이블로 적재하기 위한 프로시져

--ERROR없이 매 시간 돌 때는 파라미터 없이 SP_IN_F_SALE_SUM으로 수행 되고 수동으로 어떤 데이터의

--범위를 적재해야 할 경우 시간의 범위를 파라미터로 넘겨줌

CREATE OR REPLACE PROCEDURE

SP_IN_F_SALE_SUM(V_STARTTIME IN VARCHAR2 DEFAULT NULL,

        V_ENDTIME IN VARCHAR2 DEFAULT NULL)

AS

  D_STARTTIME DATE;

  D_ENDTIME DATE;

  V_ERR_MSG VARCHAR2(100);

BEGIN

   

 --프로시저가 파라미터 값이 없이 수행될 경우

 IF V_STARTTIME IS NULL AND V_ENDTIME IS NULL THEN

  SELECT NVL(JOB_DATAEND_TIME,TRUNC(SYSDATE-1/24,'HH24'))

  INTO D_STARTTIME

  FROM JOB_LOG

  WHERE JOB_ID=(SELECT JOB_ID FROM JOB_LIST

             WHERE JOB_NAME='SP_IN_F_SALE_SUM'

       AND JOB_EXEC_HOUR=TO_CHAR(SYSDATE-1/24,'HH24'))

  AND JOB_EXEC_DATE=TO_CHAR(SYSDATE,'YYYYMMDD')

  AND JOB_STATUS='Y';

   

  D_ENDTIME:=TRUNC(SYSDATE,'HH24');

 ELSE

  SELECT NVL(JOB_DATAEND_TIME,TO_DATE(V_STARTTIME,'YYYYMMDDHH24MISS'))

  INTO D_STARTTIME

  FROM JOB_LOG

  WHERE JOB_ID=(SELECT JOB_ID FROM JOB_LIST

             WHERE JOB_NAME='SP_IN_F_SALE_SUM'

       AND JOB_EXEC_HOUR=SUBSTR(V_STARTTIME,9,2))

  AND JOB_EXEC_DATE=SUBSTR(V_ENDTIME,1,8)

  AND JOB_STATUS='Y';

   

  D_ENDTIME:=TO_DATE(V_ENDTIME,'YYYYMMDDHH24MISS');

 END IF;

   

   

 --수행되는 프로시저의 START시간을 찍어주고 RUNNING으로 표시

 UPDATE JOB_LOG

 SET JOB_START_TIME=SYSDATE,

  JOB_STATUS='R'

 WHERE JOB_ID=(SELECT JOB_ID FROM JOB_LIST

         WHERE JOB_NAME='SP_IN_F_SALE_SUM'

      AND JOB_EXEC_HOUR=SUBSTR(V_STARTTIME,9,2))

 AND JOB_EXEC_DATE=SUBSTR(V_ENDTIME,1,8);

   

 -- DML------

   

  --수행되어질 INSERT문

   

 -- DML-----

   

 COMMIT;

   

 --프로시저가 ERROR없이 수행이 끝나면 END 시간과 가져온 데이터의 범위를 찍어줌

 UPDATE JOB_LOG

 SET JOB_END_TIME=SYSDATE,

  JOB_DATASTART_TIME=D_STARTTIME,

  JOB_DATAEND_TIME=D_ENDTIME,

  JOB_STATUS='Y'

 WHERE JOB_ID=(SELECT JOB_ID FROM JOB_LIST

         WHERE JOB_NAME='SP_IN_F_SALE_SUM'

      AND JOB_EXEC_HOUR=TO_CHAR(D_ENDTIME,'HH24'))

 AND JOB_EXEC_DATE=SUBSTR(V_ENDTIME,1,8);

   

 COMMIT;

   

EXCEPTION

   WHEN OTHERS THEN

      V_ERR_MSG:= SUBSTRB(SQLERRM, 1, 80);

      

     UPDATE JOB_LOG

     SET JOB_END_TIME=SYSDATE,

      JOB_STATUS='E',

      JOB_ERR_MSG=V_ERR_MSG

           WHERE JOB_ID=(SELECT JOB_ID FROM JOB_LIST

                  WHERE JOB_NAME='SP_IN_F_SALE_SUM'

         AND JOB_EXEC_HOUR=TO_CHAR(D_ENDTIME,'HH24'))

           AND JOB_EXEC_DATE=SUBSTR(V_ENDTIME,1,8);

      

     COMMIT;

END SP_IN_F_SALE_SUM;

/

   

   

--SP_IN_F_SALE_SUM 프로시저를 DBMS_JOB에 등록합니다.

DECLARE

    JOB_NUMBER NUMBER;

BEGIN

  DBMS_JOB.SUBMIT(JOB_NUMBER,  --JOB번호

        'SP_IN_F_SALE_SUM;',  --프로시저명

         TO_DATE('20050209000000','YYYYMMDDHH24MISS'),  --NEXT_DATE

        'TRUNC(SYSDATE,''MI'')+1/24');  --잡 수행 간격(매시간 정각)

END;

   

   

JOB의 시간이나 간격 등록된 프로시저등을 변경하고자 할 때 DBMS_JOB의 다른 프로시져를 이용해서 변경합니다.

2번째 정리 부분에 설명되어 있습니다. [출처] http://blog.naver.com/gnpado/40017478314

 

   

<http://nstyle.egloos.com/2239364>에서 삽입