Oracle 스케쥴러 관련 명령어
https://pokey.tistory.com/8
Oracle Scheduling Dates and Times
http://www.dba-oracle.com/job_scheduling/dates_times.htm
Oracle Scheduling Dates and Times
http://www.dba-oracle.com/job_scheduling/dates_times.htm
[Oracle] 프로시저(Procedure) 권한 부여 방법
https://gent.tistory.com/538?category=360526
--등록된 job
SELECT * FROM USER_SCHEDULER_JOBS;
--job의 arguments
SELECT * FROM USER_SCHEDULER_JOB_ARGS;
--현재 running중인 job들의정보
SELECT * FROM USER_SCHEDULER_RUNNING_JOBS;
--job의 log
SELECT * FROM USER_SCHEDULER_JOB_LOG;
--job의수행된정보및Error 정보
SELECT * FROM USER_SCHEDULER_JOB_RUN_DETAILS;
-- 등록된 Program
SELECT * FROM USER_SCHEDULER_PROGRAMS;
-- 프로그램의 매개변수
SELECT * FROM USER_SCHEDULER_PROGRAM_ARGS;
--등록된 스케쥴러
SELECT * FROM USER_SCHEDULER_SCHEDULES;
-- 스케쥴 삭제
BEGIN
DBMS_SCHEDULER.DROP_JOB(
JOB_NAME => 'SCH_PROC_MSG_STATE',
FORCE => false);
END ;
-- 스케쥴 시작
EXECUTE DBMS_SCHEDULER.ENABLE('SCH_PROC_MSG_STATE');
-- 스케쥴 정지
EXECUTE DBMS_SCHEDULER.DISABLE('SCH_PROC_MSG_STATE');
DBMS_JOB.submit : job 등록
DBMS_JOB.remove : 제거
DBMS_JOB.change : 변경
DBMS_JOB.next_date : job의 다음 수행시간 변경
DBMS_JOB.interval : job의 실행 cycle 지정
DBMS_JOB.what : job 수행 으로 등록된 object 를 변경
DBMS_JOB.run : job을 수동으로 실행
DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => '실행할 object'
,next_date => to_date('17-11-2007 09:00:00','dd/mm/yyyy hh24:mi:ss')
,interval => 'TRUNC(SYSDATE) + 1 + 9/24'
,no_parse => TRUE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x)); -- 이부분은 job큐의 번호가 됩니다.
END;
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'SCH_PROC_MSG_STATE_TO_DAY',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN MEM_NPRO.TEST_PROC (TO_CHAR(SYSDATE , ''YYYYMMDD'')); END;',
start_date => TO_DATE('2011-12-14 17:30:00' , 'YYYY-MM-DD HH24:MI:SS'),
repeat_interval => 'FREQ=MINUTELY;INTERVAL=30',
end_date => NULL,
enabled => TRUE,
comments => 'HJ SCH_PROC_MSG_STATE_TO_DAY');
END;
Execute daily 'SYSDATE + 1'
Execute once per week 'SYSDATE + 7'
Execute hourly 'SYSDATE + 1/24'
Execute every 10 min. 'SYSDATE + 10/1440'
Execute every 30 sec. 'SYSDATE + 30/86400'
Do not re-execute NULL
1. 10분간격으로 실행
SYSDATE + 1/24/6
2. 현재 시간으로 부터 다음 날 현재 시간에 실행 (매일)
SYSDATE + 1 -- 지금이 오후3시면 다음날 오후 3시 에 매일매일 실행됩니다.
3. 매일 새벽 5시
TRUNC(SYSDATE) + 1 + 5 / 24
4. 매일 밤 10시
TRUNC(SYSDATE) + 20 / 24
5. 예제
5-1. 매일 오전 6시
BEGIN
SYS.DBMS_JOB.INTERVAL(:JOB, 'SYSDATE+30/1440');
COMMIT;
END;
/
5-2. 매월 1일 밤 12시
BEGIN
SYS.DBMS_JOB.INTERVAL(:JOB, 'TRUNC(LAST_DAY(SYSDATE)) + 1');
COMMIT;
END;
/
5-3. 매월 1일 오전 7시 30분
BEGIN
SYS.DBMS_JOB.INTERVAL(:JOB, 'TRUNC(LAST_DAY(SYSDATE)) + 1 + 8/24 + 30/1440');
COMMIT;
END;
/
----------------------------------------------------------------------------------------------------------------------------------------------------------------
DECLARE
user_name varchar2(30);
BEGIN
select user into user_name from dual;
execute immediate 'alter session set current_schema = USERDBA';
BEGIN
SYS.DBMS_JOB.REMOVE(6);
execute immediate 'alter session set current_schema = ' || user_name ;
EXCEPTION
WHEN OTHERS THEN
execute immediate 'alter session set current_schema = ' || user_name ;
RAISE;
END;
COMMIT;
END;
/
DECLARE
X NUMBER;
user_name varchar2(30);
BEGIN
select user into user_name from dual;
execute immediate 'alter session set current_schema = USERDBA';
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'USERDBA.PR_0001SD_CONTROL;'
,next_date => to_date('27/01/2023 17:35:34','dd/mm/yyyy hh24:mi:ss')
,interval => 'ADD_MONTHS(SYSDATE, 3)'
,no_parse => FALSE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
execute immediate 'alter session set current_schema = ' || user_name ;
EXCEPTION
WHEN OTHERS THEN
execute immediate 'alter session set current_schema = ' || user_name ;
RAISE;
END;
COMMIT;
END;
/
----------------------------------------------------------------------------------------------------------------------------------------------------------------
DECLARE
user_name varchar2(30);
BEGIN
select user into user_name from dual;
execute immediate 'alter session set current_schema = USERDBA';
BEGIN
SYS.DBMS_JOB.REMOVE(6);
execute immediate 'alter session set current_schema = ' || user_name ;
EXCEPTION
WHEN OTHERS THEN
execute immediate 'alter session set current_schema = ' || user_name ;
RAISE;
END;
COMMIT;
END;
/
DECLARE
X NUMBER;
user_name varchar2(30);
BEGIN
select user into user_name from dual;
execute immediate 'alter session set current_schema = USERDBA';
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'USERDBA.PR_0001SD_CONTROL;'
,next_date => to_date('27/01/2023 17:35:34','dd/mm/yyyy hh24:mi:ss')
,interval => 'SYSDATE+10/1440'
,no_parse => FALSE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
execute immediate 'alter session set current_schema = ' || user_name ;
EXCEPTION
WHEN OTHERS THEN
execute immediate 'alter session set current_schema = ' || user_name ;
RAISE;
END;
COMMIT;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
DECLARE
user_name varchar2(30);
BEGIN
select user into user_name from dual;
execute immediate 'alter session set current_schema = USERDBA';
BEGIN
SYS.DBMS_JOB.REMOVE(6);
execute immediate 'alter session set current_schema = ' || user_name ;
EXCEPTION
WHEN OTHERS THEN
execute immediate 'alter session set current_schema = ' || user_name ;
RAISE;
END;
COMMIT;
END;
/
DECLARE
X NUMBER;
user_name varchar2(30);
BEGIN
select user into user_name from dual;
execute immediate 'alter session set current_schema = USERDBA';
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'USERDBA.PR_0001SD_CONTROL;'
,next_date => to_date('27/01/2023 17:35:34','dd/mm/yyyy hh24:mi:ss')
,interval => 'TRUNC(SYSDATE+1) + (3/24)'
,no_parse => FALSE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
execute immediate 'alter session set current_schema = ' || user_name ;
EXCEPTION
WHEN OTHERS THEN
execute immediate 'alter session set current_schema = ' || user_name ;
RAISE;
END;
COMMIT;
END;
/
