[Oracle] JOB 등록/정지/삭제/변경 방법

1. JOB 등록

set serveroutput on

DECLARE
    X NUMBER;
BEGIN
   SYS.DBMS_JOB.SUBMIT
   ( job            => X
    ,what          => 'DECLARE
                          VAR1 VARCHAR2(200);
                          VAR2 VARCHAR2(200);
                          VAR3 VARCHAR2(200);
                       BEGIN
                          VAR1 := ''test1'';
                          VAR2 := ''test2'';
                          VAR3 := ''test3'';
                       
                          TEST.FUNCTION1( VAR1,VAR2,VAR3 );
                       
                          COMMIT;
                       END;'
    ,next_date   => to_date('20190801 06:45','yyyymmdd hh24:mi')
    ,interval      => 'TRUNC(SYSDATE+1) + 6/24 + 45/1440'
    ,no_parse   => TRUE
   );
   SYS.DBMS_OUTPUT.PUT_LINE('Job Number is : ' || to_char(x));
END;
/

COMMIT;

2. JOB 정지

EXECUTE  dbms_job.broken(100, FALSE);
COMMIT;

주) 100(바로위에꺼) 는 JOB 번호
 

 

3. JOB 삭제

EXECUTE  dbms_job.REMOVE(100);
COMMIT;

주) 100(바로위에꺼) 는 JOB 번호
 

4. JOB 변경 NEXT_DATE

EXECUTE DBMS_JOB.NEXT_DATE(4, TRUNC(SYSDATE)+1+1/24/60);
COMMIT;

5. JOB 실행

EXECUTE DBMS_JOB.RUN(100);
COMMIT;