[Oracle] ORACLE JOB 조회, 등록, 삭제, 실행, 변경

300x250

 

-- JOB 조회

select * from user_jobs;

 

-- JOB 조회, dba_jobs 테이블에서 정보 확인

select job, what, failures, total_time, last_date, last_sec, next_date, next_sec, interval

from dba_jobs order by next_date;

 

-- JOB 조회 dba_jobs 테블에서 확인

select job, what, last_date, next_date, interval

from dba_jobs order by next_date;

 

-- JOB 등록

EXECUTE dbms_job.submit( JOBNO, 'USER_NAME.PROCEDURE_NAME;', TRUNC(SYSDATE)+23/24, 'TRUNC(SYSDATE+1)+23/24', FALSE);

commit;

 

--> ex>

EXECUTE dbms_job.submit(

JOB번호, JOB내용, JOB 실행시간, JOB실행되는간격, FALSE)

 

JOB내용 : ' ' 으로 감싸고, USER를 반드시 확인 할것

             운영은 US_DBA, 개발을 각 USER

 

-- JOB 정지

EXECUTE dbms_job.broken(JOB번호, FALSE);

commit;

 

--> ex> EXECUTE dbms_job.broken(143, FALSE);

 

-- JOB 삭제

EXECUTE dbms_job.REMOVE(JOB번호);

 

 

-- JOB 변경 NEXT_DATE

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

 

-- JOB 실행

EXECUTE DBMS_JOB.RUN(JOB번호);

commit;

 

---

출처 : https://tnsgud.tistory.com/entry/ORACLE-JOB-조회-등록-삭제-실행-변경 

ORACLE JOB 조회, 등록, 삭제, 실행, 변경