DBMS_JOB, DBMS_SCHEDULER 중지 하기
<< 요약 >>
DBMS_SCHEDULER
: DBA 가 강제로 다른 유저의 SCHEDULER DISABLE 가능함
DBMS_JOB
: DBA 가 강제로 다른 유저의 JOB BROKEN 불가
JOB OWNER 만 JOB BROKEN 가능함
JOB OWNER PROCEDUER 를 통해서 우회적으로 BROKEN 작업
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 | ----------------------------------------------------------------------------------------------------------------------- -- SCHEUDLER ----------------------------------------------------------------------------------------------------------------------- -- 테스트 유저 생성 및 신규 SCHEDULER 등록 SYS@TGDB=> create user test identified by test default tablespace users 2 temporary tablespace temp quota unlimited on users ; User created. SYS@TGDB=> grant connect, create table to test ; Grant succeeded. SYS@TGDB=> grant execute on dbms_lock to test ; Grant succeeded. SYS@TGDB=> grant create job to test ; Grant succeeded. SYS@TGDB=> grant select any dictionary to test ; Grant succeeded. SYS@TGDB=> grant create procedure to test; Grant succeeded. SYS@TGDB=> conn test/test Connected. TEST@TGDB=> create table jobtest1 ( a date) ; Table created. TEST@TGDB=> create table jobtest2 ( a date) ; Table created. TEST@TGDB=> begin 2 dbms_scheduler.create_job( 3 job_name=>'jobtest1_job', 4 job_type=>'PLSQL_BLOCK', 5 job_action=>'BEGIN insert into jobtest1 values ( sysdate); commit; end;', 6 start_date => sysdate, 7 repeat_interval => 'sysdate+3/1440', 8 enabled => false, 9 auto_drop => false) ; 10 end; 11 / PL/SQL procedure successfully completed. TEST@TGDB=> select * from jobtest1; no rows selected TEST@TGDB=> exec dbms_scheduler.enable('JOBTEST1_JOB'); PL/SQL procedure successfully completed. TEST@TGDB=> SELECT OWNER, JOB_NAME, JOB_ACTION, ENABLED 2 FROM DBA_SCHEDULER_JOBS 3 WHERE OWNER ='TEST' 4 ORDER BY OWNER, JOB_NAME ; OWNER JOB_NAME JOB_ACTION ENABLED -------------- ----------------------------- ------------------------------------------------------------ ---------- TEST JOBTEST1_JOB BEGIN insert into jobtest1 values ( sysdate); commit; end; TRUE TEST@TGDB=> TEST@TGDB=> select * from jobtest1; A --------- 07-MAR-18 TEST@TGDB=> -- 다른 유저 SCHEDULER DISABLE 시키기 SYS@TGDB=> exec dbms_scheduler.disable('TEST.JOBTEST1_JOB'); PL/SQL procedure successfully completed. SYS@TGDB=> col owner for a14 SYS@TGDB=> col job_name for a29 SYS@TGDB=> col job_action for a70 SYS@TGDB=> col enabled for a10 SYS@TGDB=> set linesize 170 SYS@TGDB=> set pagesize 200 SYS@TGDB=> SYS@TGDB=> SELECT OWNER, JOB_NAME, JOB_ACTION, ENABLED 2 FROM DBA_SCHEDULER_JOBS 3 WHERE OWNER ='TEST' 4 ORDER BY OWNER, JOB_NAME ; OWNER JOB_NAME JOB_ACTION ENABLED -------------- ----------------------------- ------------------------------------------------------------ ---------- TEST JOBTEST1_JOB BEGIN insert into jobtest1 values ( sysdate); commit; end; FALSE SYS@TGDB=> | cs |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 | ----------------------------------------------------------------------------------------------------------------------- -- JOB ----------------------------------------------------------------------------------------------------------------------- -- JOB 등록 및 OWNER / 다른 유저로 활성화/비활성화 SYS@TGDB=> conn test/test Connected. TEST@TGDB=> create table jobtest2 ( a date ) ; Table created. TEST@TGDB=> create or replace procedure job_log_proc 2 is 3 begin 4 insert into jobtest2 values ( sysdate ) ; 5 commit ; 6 end ; 7 / Procedure created. TEST@TGDB=> show error No errors. TEST@TGDB=> var job_no number TEST@TGDB=> exec dbms_job.submit(:job_no,'job_log_proc;',sysdate,'sysdate+1/24/60'); PL/SQL procedure successfully completed. TEST@TGDB=> print job_no JOB_NO ---------- 229 -- JOB 등록 확인 1분마다 수행 , BRKOEN = N (활성화 ) TEST@TGDB=> set lines 180 pages 200 TEST@TGDB=> col log_user for a12 TEST@TGDB=> col schema_user for a12 TEST@TGDB=> col broken for a8 TEST@TGDB=> col interval for a15 TEST@TGDB=> col failures for 999 TEST@TGDB=> col what for a15 TEST@TGDB=> col L_DT for a20 TEST@TGDB=> col N_DT for a20 TEST@TGDB=> select log_user, schema_user, BROKEN, INTERVAL, FAILURES, WHAT 2 , to_char(last_date, 'YYYY/MM/DD:HH24:MI:SS') AS L_DT 3 , to_char(next_date, 'YYYY/MM/DD:HH24:MI:SS') AS N_DT 4 from dba_jobs where JOB = :job_no ; LOG_USER SCHEMA_USER BROKEN INTERVAL FAILURES WHAT L_DT N_DT ------------ ------------ -------- --------------- -------- --------------- -------------------- -------------------- TEST TEST N sysdate+1/24/60 job_log_proc; 2018/03/08:11:43:10 -- JOB MANUAL 수행 TEST@TGDB=> exec dbms_job.run(:job_no) ; PL/SQL procedure successfully completed. TEST@TGDB=> select log_user, schema_user, BROKEN, INTERVAL, FAILURES, WHAT 2 , to_char(last_date, 'YYYY/MM/DD:HH24:MI:SS') AS L_DT 3 , to_char(next_date, 'YYYY/MM/DD:HH24:MI:SS') AS N_DT 4 from dba_jobs where JOB = :job_no ; LOG_USER SCHEMA_USER BROKEN INTERVAL FAILURES WHAT L_DT N_DT ------------ ------------ -------- --------------- -------- --------------- -------------------- -------------------- TEST TEST N sysdate+1/24/60 0 job_log_proc; 2018/03/08:11:43:35 2018/03/08:11:44:35 TEST@TGDB=> select * from jobtest2 order by a desc ; A --------- 08-MAR-18 08-MAR-18 -- OWNER 로 JOB BROKWN(비활성화) TEST@TGDB=> exec dbms_job.broken(:job_no,TRUE); PL/SQL procedure successfully completed. TEST@TGDB=> select log_user, schema_user, BROKEN, INTERVAL, FAILURES, WHAT 2 , to_char(last_date, 'YYYY/MM/DD:HH24:MI:SS') AS L_DT 3 , to_char(next_date, 'YYYY/MM/DD:HH24:MI:SS') AS N_DT 4 from dba_jobs where JOB = :job_no ; LOG_USER SCHEMA_USER BROKEN INTERVAL FAILURES WHAT L_DT N_DT ------------ ------------ -------- --------------- -------- --------------- -------------------- -------------------- TEST TEST Y sysdate+1/24/60 0 job_log_proc; 2018/03/08:11:43:35 4000/01/01:00:00:00 TEST@TGDB=> exec dbms_job.broken(:job_no,FALSE); PL/SQL procedure successfully completed. TEST@TGDB=> select log_user, schema_user, BROKEN, INTERVAL, FAILURES, WHAT 2 , to_char(last_date, 'YYYY/MM/DD:HH24:MI:SS') AS L_DT 3 , to_char(next_date, 'YYYY/MM/DD:HH24:MI:SS') AS N_DT 4 from dba_jobs where JOB = :job_no ; LOG_USER SCHEMA_USER BROKEN INTERVAL FAILURES WHAT L_DT N_DT ------------ ------------ -------- --------------- -------- --------------- -------------------- -------------------- TEST TEST N sysdate+1/24/60 0 job_log_proc; 2018/03/08:11:43:35 4000/01/01:00:00:00 TEST@TGDB=> TEST@TGDB=> TEST@TGDB=> TEST@TGDB=> commit ; Commit complete. -- 다른 유저로 JOB DISABLE 시키기, JOB OWNER 가 아니라 BROKEN 불가함 !!! SYS@TGDB=> exec dbms_job.broken(229,TRUE); BEGIN dbms_job.broken(229,TRUE); END; * ERROR at line 1: ORA-23421: job number 229 is not a job in the job queue ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86 ORA-06512: at "SYS.DBMS_IJOB", line 781 ORA-06512: at "SYS.DBMS_JOB", line 254 ORA-06512: at line 1 SYS@TGDB=> -- JOB BROKEN 을 위한 OWNER 프로시져 작성 SYS@TGDB=> CREATE OR REPLACE PROCEDURE TEST.DISABLE_JOB_PRC 2 IS 3 BEGIN 4 dbms_job.broken(229,TRUE); 5 commit; 6 END; 7 / Procedure created. SYS@TGDB=> set lines 180 pages 200 SYS@TGDB=> col log_user for a12 SYS@TGDB=> col schema_user for a12 SYS@TGDB=> col broken for a8 SYS@TGDB=> col interval for a15 SYS@TGDB=> col failures for 999 SYS@TGDB=> col what for a15 SYS@TGDB=> col L_DT for a20 SYS@TGDB=> col N_DT for a20 SYS@TGDB=> select log_user, schema_user, BROKEN, INTERVAL, FAILURES, WHAT 2 , to_char(last_date, 'YYYY/MM/DD:HH24:MI:SS') AS L_DT 3 , to_char(next_date, 'YYYY/MM/DD:HH24:MI:SS') AS N_DT 4 from dba_jobs where JOB = 229 ; LOG_USER SCHEMA_USER BROKEN INTERVAL FAILURES WHAT L_DT N_DT ------------ ------------ -------- --------------- -------- --------------- -------------------- -------------------- TEST TEST N sysdate+1/24/60 0 job_log_proc; 2018/03/08:11:44:38 2018/03/08:11:50:24 SYS@TGDB=> SYS@TGDB=> EXEC TEST.DISABLE_JOB_PRC; PL/SQL procedure successfully completed. SYS@TGDB=> SYS@TGDB=> set lines 180 pages 200 SYS@TGDB=> col log_user for a12 SYS@TGDB=> col schema_user for a12 SYS@TGDB=> col broken for a8 SYS@TGDB=> col interval for a15 SYS@TGDB=> col failures for 999 SYS@TGDB=> col what for a15 SYS@TGDB=> col L_DT for a20 SYS@TGDB=> col N_DT for a20 SYS@TGDB=> select log_user, schema_user, BROKEN, INTERVAL, FAILURES, WHAT 2 , to_char(last_date, 'YYYY/MM/DD:HH24:MI:SS') AS L_DT 3 , to_char(next_date, 'YYYY/MM/DD:HH24:MI:SS') AS N_DT 4 from dba_jobs where JOB = 229 ; LOG_USER SCHEMA_USER BROKEN INTERVAL FAILURES WHAT L_DT N_DT ------------ ------------ -------- --------------- -------- --------------- -------------------- -------------------- TEST TEST Y sysdate+1/24/60 0 job_log_proc; 2018/03/08:11:44:38 4000/01/01:00:00:00 SYS@TGDB=> SYS@TGDB=> | cs |