dbms_scheduler example prompt Show Scheduler database objects TTITLE 'Scheduler Objects:' COL owner FORMAT A20 HEADING 'Owner' COL object_type FORMAT A12 HEADING 'Object|Type' COL object_name FORMAT A30 HEADING 'Name' COL created FORMAT A20 HEADING 'Created On' COL status FORMAT A12 HEADING 'Status' SELECT owner ,object_type ,object_name ,created ,status FROM dba_objects WHERE object_type IN ('PROGRAM', 'JOB', 'JOB CLASS', 'SCHEDULE', 'WINDOW') ORDER BY object_type, OBJECT_name; Object Owner Type Name Created On Status -------------------- ------------ ------------------------------ -------------------- ------------ SYS JOB BSLN_MAINTAIN_STATS_JOB 04-JAN-19 VALID SYS JOB CLEANUP_NON_EXIST_OBJ 04-JAN-19 VALID prompt Show Schedule objects prompt 'Schedules:' COL owner FORMAT A08 HEADING 'Owner' COL schedule_name FORMAT A30 HEADING 'Schedule' COL start_date FORMAT A20 HEADING 'Starts|On' COL end_date FORMAT A20 HEADING 'Ends|On' COL repeat_interval FORMAT A45 HEADING 'Interval' SELECT owner ,schedule_name ,to_char(start_date, 'mm/dd/yyyy hh24:mi:ss') start_date ,to_char(end_date, 'mm/dd/yyyy hh24:mi:ss') end_date ,repeat_interval FROM dba_scheduler_schedules; prompt Show Program objects : What program objects are available? TTITLE 'Programs:' COL owner FORMAT A08 HEADING 'Owner' COL program_name FORMAT A30 HEADING 'Program' COL program_type FORMAT A16 HEADING 'Type' COL program_action FORMAT A60 HEADING 'Action' SELECT owner ,program_name ,program_type ,program_action FROM dba_scheduler_programs; Owner Program Type Action -------- ------------------------------ ---------------- ------------------------------------------------------------ SYS PURGE_LOG_PROG STORED_PROCEDURE dbms_scheduler.auto_purge SYS FILE_WATCHER_PROGRAM STORED_PROCEDURE dbms_isched.file_watch_job SYS PMO_DEFERRED_GIDX_MAINT PLSQL_BLOCK dbms_part.cleanup_gidx_internal( prompt What program's arguments are attached? TTITLE 'Program Arguments:' COL owner FORMAT A20 HEADING 'Owner' COL program_name FORMAT A30 HEADING 'Program' COL argument_name FORMAT A12 HEADING 'Arg Name' COL argument_position FORMAT 999 HEADING 'Arg|Pos' COL argument_type FORMAT A25 HEADING 'Arg Type' COL default_value FORMAT A12 HEADING 'Default|Value' COL out_argument FORMAT A06 HEADING 'Out|Arg?' SELECT owner ,program_name ,argument_name ,argument_position ,argument_type ,default_value ,out_argument FROM dba_scheduler_program_args; Arg Default Out Owner Program Arg Name Pos Arg Type Value Arg? -------------------- ------------------------------ ------------ ---- ------------------------- ------------ ------ SYS HS_PARALLEL_SAMPLING 9 SYS.HSBLKNAMLSTFALSE SYS HS_PARALLEL_SAMPLING 10 SYS.HSBLKNAMLST FALSE prompt Show Job objects COL owner FORMAT A20 HEADING 'Owner' COL job_name FORMAT A30 HEADING 'Job' COL program_name FORMAT A20 HEADING 'Program|Name' COL job_class FORMAT A35 HEADING 'Job Class' COL job_type FORMAT A12 HEADING 'Job|Type' COL job_action FORMAT A50 HEADING 'Job|Action' prompt 'Jobs:---- ' SELECT owner ,job_name ,state ,job_class ,job_type ,job_action FROM dba_scheduler_jobs; Job Job Owner Job State Job Class Type Action -------------------- ------------------------------ --------------- ----------------------------------- ------------ -------------------------------------------------- SYS PURGE_LOG SCHEDULED DEFAULT_JOB_CLASS SYS FILE_WATCHER DISABLED SCHED$_LOG_ON_ERRORS_CLASS COL owner FORMAT A08 HEADING 'Owner' COL job_name FORMAT A30 HEADING 'Job' COL program_name FORMAT A30 HEADING 'Program|Name' COL schedule_name FORMAT A30 HEADING 'Schedule|Name' COL enabled FORMAT A08 HEADING 'Enabled?' COL state FORMAT A15 HEADING 'State' COL restartable FORMAT A08 HEADING 'Restart|-able?' COL start_date FORMAT A37 HEADING 'Start|Date' prompt 'Job Components:' SELECT owner ,job_name ,program_name ,schedule_name ,enabled ,state ,restartable ,start_date FROM dba_scheduler_jobs; Program Schedule Restart Start Owner Job Name Name Enabled? State -able? Date -------- ------------------------------ ------------------------------ ------------------------------ -------- --------------- -------- ------------------------------------- SYS PURGE_LOG PURGE_LOG_PROG DAILY_PURGE_SCHEDULE TRUE SCHEDULED FALSE 08-JUL-19 03.00.00.584504 AM ETC/GMT SYS FILE_WATCHER FILE_WATCHER_PROGRAM FILE_WATCHER_SCHEDULE FALSE DISABLED FALSE prompt What are a Job's arguments? COL job_name FORMAT A30 HEADING 'Job' COL argument_name FORMAT A12 HEADING 'Arg Name' COL argument_position FORMAT 999 HEADING 'Arg|Pos' COL value FORMAT A32 HEADING 'Argument Value' prompt 'Job Arguments:' SELECT job_name ,argument_name ,argument_position ,value FROM dba_scheduler_job_args; Arg Job Arg Name Pos Argument Value ------------------------------ ------------ ---- -------------------------------- DRA_REEVALUATE_OPEN_FAILURES 1 TRUE DRA_REEVALUATE_OPEN_FAILURES 2 TRUE DRA_REEVALUATE_OPEN_FAILURES 3 TRUE DRA_REEVALUATE_OPEN_FAILURES 4 900 prompt Show Scheduled Tasks History: Show a high-level view of scheduled task execution history COL log_id FORMAT 9999 HEADING 'Log#' COL log_date FORMAT A37 HEADING 'Log Date' COL owner FORMAT A06 HEADING 'Owner' COL job_name FORMAT A30 HEADING 'Job' COL status FORMAT A10 HEADING 'Status' prompt 'Scheduled Tasks History:' SELECT log_id ,log_date ,owner ,job_name ,status FROM dba_scheduler_job_log where 1=1 and log_date>sysdate -1 ; Log# Log Date Owner Job Status ----- ------------------------------------- ------ ------------------------------ ---------- 7344 14-MAR-23 10.00.00.355353 PM +00:00 SYS ORA$AT_OS_OPT_SY_230623 7346 14-MAR-23 10.00.00.357186 PM +00:00 SYS ORA$AT_OS_OPT_SY_230623 7348 14-MAR-23 10.00.00.358230 PM +00:00 SYS ORA$AT_OS_OPT_SY_230623 7350 14-MAR-23 10.00.00.360661 PM +00:00 SYS ORA$AT_SA_SPC_SY_230624 prompt What scheduled tasks failed during execution, and Error details COL log_id FORMAT 9999 HEADING 'Log#' COL log_date FORMAT A32 HEADING 'Log Date' COL owner FORMAT A06 HEADING 'Owner' COL job_name FORMAT A20 HEADING 'Job' COL status FORMAT A10 HEADING 'Status' COL actual_start_date FORMAT A32 HEADING 'Actual|Start|Date' COL error# FORMAT 999999 HEADING 'Error|Nbr' prompt 'Scheduled Tasks That Failed:' SELECT log_id ,log_date ,owner ,job_name ,status ,actual_start_date ,error# FROM dba_scheduler_job_run_details WHERE status <> 'SUCCEEDED' ORDER BY actual_start_date; prompt Which jobs are running right now ? COL owner FORMAT A06 HEADING 'Owner' COL job_name FORMAT A20 HEADING 'Job' COL session_id FORMAT 999999 HEADING 'Session' COL running_instance FORMAT A10 HEADING 'Running|Instance' COL elapsed_time FORMAT A12 HEADING 'Elapsed|Time' TTITLE 'Scheduled Tasks Running Right Now:' SELECT owner ,job_name ,session_id ,running_instance ,elapsed_time FROM dba_scheduler_running_jobs; column job_name format a20 column status format a12 column actual_start_date format a36 column run_duration format a14 select job_name, status, actual_start_date, run_duration from dba_scheduler_job_run_details where job_name = 'GATHER_STATS_JOB' order by actual_start_date ; select error#, status from dba_scheduler_job_run_details where status like '%FAIL%'; set feedback off set echo off set lines 205 column owner format a15 column job_name format a22 column program_name format a24 column status format a10 column state format a10 column last_start format a25 column last_start_norm format a18 column "DURATION (d:hh:mm:ss)" format a21 column next_run format a25 column next_run_norm format a18 var local_offset number begin select extract(timezone_hour from systimestamp) into :local_offset from dual; end; / select dsj.owner, dsj.job_name, dsj.program_name, dsjlmax.status, dsj.state, -- to_char(dsj.last_start_date,'dd-mon-yyyy hh24:mi TZH:TZM') last_start, to_char(dsj.last_start_date + (:local_offset-extract(timezone_hour from dsj.last_start_date))/24,'dd-mon-yyyy hh24:mi') last_start_norm, extract(day from dsj.last_run_duration) ||':'|| lpad(extract(hour from dsj.last_run_duration),2,'0')||':'|| lpad(extract(minute from dsj.last_run_duration),2,'0')||':'|| lpad(round(extract(second from dsj.last_run_duration)),2,'0') "DURATION (d:hh:mm:ss)", -- to_char(dsj.next_run_date,'dd-mon-yyyy hh24:mi TZH:TZM') next_run, to_char(dsj.next_run_date + (:local_offset-extract(timezone_hour from dsj.next_run_date))/24,'dd-mon-yyyy hh24:mi') next_run_norm from dba_scheduler_jobs dsj, (select job_name, status from dba_scheduler_job_log dsjl where log_date = (select max(log_date) from dba_scheduler_job_log dsjl2 where dsjl2.job_name = dsjl.job_name) ) dsjlmax where dsj.owner in ('SYSTEM','SYS') and dsj.job_name = dsjlmax.job_name;========== dbms_scheduler example begin dbms_scheduler.create_job( job_name => 'SCOTT_JOB_SCHEDULE' ,job_type => 'PLSQL_BLOCK' ,job_action => 'begin dbms_stats.gather_schema_stats(ownname =>"SCOTT", estimate_percent => 30, cascade=>true, method_opt=> 'for all columns size 1', degree => 5 , options => 'GATHER STALE '); end; ' ,start_date => trunc(sysdate + 1) + 6/24 ,repeat_interval => 'FREQ=DAILY' ,enabled => TRUE ,comments => 'customized stats gathering for risk engine'); end; begin dbms_scheduler.create_job ( Job_name=> 'anuj_job', Job_type=> 'PLSQL_BLOCK', Job_action=> 'update employees set salary = salary * 1.5;', Start_date=> '10-dec-2011 06:00:00 pm', Repeat_interval=> 'FREQ=YEARLY', Comments=> 'testing..... ' ); end; / PL/SQL procedure successfully completed. SQL> select job_name, enabled, run_count from user_scheduler_jobs; JOB_NAME ENABL RUN_COUNT ------------------------------ ----- ---------- SM$CLEAN_AUTO_SPLIT_MERGE TRUE 1 RSE$CLEAN_RECOVERABLE_SCRIPT TRUE 1 PURGE_LOG TRUE 1 ORA$AUTOTASK_CLEAN TRUE 1 HM_CREATE_OFFLINE_DICTIONARY FALSE 0 FGR$AUTOPURGE_JOB FALSE 0 DRA_REEVALUATE_OPEN_FAILURES TRUE 0 BSLN_MAINTAIN_STATS_JOB TRUE 1 ANUJ_JOB FALSE 0 FILE_WATCHER FALSE 0 XMLDB_NFS_CLEANUP_JOB FALSE 0 11 rows selected. exec dbms_scheduler.copy_job('anuj_job','anuj_job_copy'); SQL> exec dbms_scheduler.copy_job('anuj_job','anuj_job_copy'); PL/SQL procedure successfully completed. exec dbms_scheduler.stop_job(job_name=> 'anuj_job', force=> true); SQL> exec dbms_scheduler.drop_job('anuj_job'); PL/SQL procedure successfully completed. SQL> select job_name, enabled, run_count from user_scheduler_jobs; JOB_NAME ENABL RUN_COUNT ------------------------------ ----- ---------- ANUJ_JOB_COPY FALSE 0 SM$CLEAN_AUTO_SPLIT_MERGE TRUE 1 RSE$CLEAN_RECOVERABLE_SCRIPT TRUE 1 FGR$AUTOPURGE_JOB FALSE 0 BSLN_MAINTAIN_STATS_JOB TRUE 1 DRA_REEVALUATE_OPEN_FAILURES TRUE 0 HM_CREATE_OFFLINE_DICTIONARY FALSE 0 ORA$AUTOTASK_CLEAN TRUE 1 PURGE_LOG TRUE 1 FILE_WATCHER FALSE 0 XMLDB_NFS_CLEANUP_JOB FALSE 0 11 rows selected. exec dbms_scheduler.run_job('ANUJ_JOB_COPY', true); exec dbms_scheduler.run_job('ANUJ_JOB_COPY', true); SQL> exec dbms_scheduler.set_attribute ( name => 'ANUJ_JOB_COPY',attribute => 'job_priority', value => 1 ); PL/SQL procedure successfully completed. EXEC dbms_scheduler.run_job('ANUJ_JOB_COPY') ; BEGIN DBMS_SCHEDULER.ENABLE('ANUJ_JOB_COPY'); END; PL/SQL procedure successfully completed. BEGIN DBMS_SCHEDULER.DISABLE('ANUJ_JOB_COPY'); END; BEGIN DBMS_SCHEDULER.create_job ( job_name => 'anuj_full_job1', job_type => 'PLSQL_BLOCK', job_action => 'BEGIN DBMS_STATS.gather_schema_stats(''SCOTT''); END;', start_date => SYSTIMESTAMP, repeat_interval => 'freq=hourly; byminute=0', end_date => NULL, enabled => TRUE, comments => 'Job defined entirely by the CREATE JOB procedure.'); END; PL/SQL procedure successfully completed. BEGIN DBMS_SCHEDULER.RUN_JOB(job_name => 'anuj_full_job1'); END; DECLARE l_job NUMBER := 0; BEGIN DBMS_JOB.SUBMIT(l_job,'anuj_full_job;',sysdate,'TRUNC(sysdate)+1+1/288'); END; BEGIN DBMS_SCHEDULER.ENABLE('ANUJ_FULL_JOB'); END; EXEC dbms_scheduler.run_job('ANUJ_FULL_JOB'); EXEC dbms_scheduler.run_job('ANUJ_FULL_JOB'); PL/SQL procedure successfully completed. SQL> select job_name, enabled, run_count from user_scheduler_jobs; JOB_NAME ENABL RUN_COUNT ------------------------------ ----- ---------- ANUJ_JOB_COPY TRUE 0 ANUJ_FULL_JOB TRUE 0 SM$CLEAN_AUTO_SPLIT_MERGE TRUE 1 RSE$CLEAN_RECOVERABLE_SCRIPT TRUE 1 FGR$AUTOPURGE_JOB FALSE 0 BSLN_MAINTAIN_STATS_JOB TRUE 1 DRA_REEVALUATE_OPEN_FAILURES TRUE 0 HM_CREATE_OFFLINE_DICTIONARY FALSE 0 ORA$AUTOTASK_CLEAN TRUE 1 PURGE_LOG TRUE 1 FILE_WATCHER FALSE 0 XMLDB_NFS_CLEANUP_JOB FALSE 0 12 rows selected. SELECT job_name, operation, status, user_name FROM dba_scheduler_job_log WHERE operation != 'RUN' and owner = 'SCOTT'; SELECT * FROM dba_scheduler_jobs Monitoring job-scheduling Jobs can be monitored using Oracle Enterprise Manager 10g. It's also possible to use a number of views that have been created in Oracle 10g. We will discuss some of these views here. To show details on job run: select log_date , job_name , status , req_start_date , actual_start_date , run_duration from dba_scheduler_job_run_details To show running jobs: select job_name , session_id , running_instance , elapsed_time , cpu_used from dba_scheduler_running_jobs; To show job history: select log_date , job_name , status from dba_scheduler_job_log; show all schedules: select schedule_name, schedule_type, start_date, repeat_interval from dba_scheduler_schedules; show all jobs and their attributes: http://www.oracledatabase12g.com/archives/%E4%BA%86%E8%A7%A3sysdate%E5%87%BD%E6%95%B0.html select * from dba_scheduler_jobs show all program-objects and their attributes select * from dba_scheduler_programs; show all program-arguments: select * from dba_scheduler_program_args; select to_char(sysdate,'YEAR-MON-DAY HH24:MI:SS') from dual; SQL> select to_char(sysdate,'YEAR-MON-DAY HH24:MI:SS') from dual; TO_CHAR(SYSDATE,'YEAR-MON-DAYHH24:MI:SS') ----------------------------------------------------------------- TWENTY ELEVEN-DEC-SATURDAY 22:59:13 set linesize 200 pagesize 1400; col sessiontimezone for a20 SQL> select current_date, sysdate,dbtimezone,sessiontimezone from dual; CURRENT_DATE SYSDATE DBTIME SESSIONTIMEZONE ------------------- ------------------- ------ -------------------- 2011-08-24 19:53:09 2011-08-24 19:53:09 +08:00 +08:00 select current_date, sysdate,dbtimezone,sessiontimezone from dual; CURRENT_D SYSDATE DBTIME SESSIONTIMEZONE --------- --------- ------ --------------------------------------------------------------------------- 10-DEC-11 10-DEC-11 +00:00 +00:00 Window issues: Note 742683.1 Scheduled Job Works Does Not Start In Window. Purging Diagnostics Note 443364.1 How To Purge DBA_SCHEDULER_JOB_LOG and DBA_SCHEDULER_WINDOW_LOG Note 749440.1 Dbms_scheduler.Purge Not Removing Entries from dba_scheduler_job_run_details Schedule issues: Note 312547.1 Advanced use of DBMS_JOB.SUBMIT 'interval' Parameter Note 428872.1 Scheduled Jobs Do Not Run After A Re-Start With Repeat_interval => Null. Issues with Statistic Jobs: Note 430636.1 GATHER_STATS_JOB Is Not Running Note 377143.1 How to check what automatic statistics collection is scheduled on 10g Note 803191.1 Checklist To Diagnose Issues Related To Automatic Run Of Job GATHER_STATS_JOB Issues with external Jobs: http://forums.oracle.com/forums/thread.jspa?threadID=555102 NOTE:389685.1 – Items to Check when Problems Running Executables through the Scheduler NOTE:976049.1 DBMS_SCHEDULER fails with ORA-27369 and STANDARD_ERROR=”Launching external job failed: Invalid username or password” NOTE:1300215.1 ORA-27369: job of type EXECUTABLE failed with exit code: 274667 Please note that this article will guide through the correct analysis for Scheduler/Job Problems. It is also practicable to go through a list of known issues / bugs which will be listed in NOTE:1311355.1 Known Issues And Bugs With Using the Scheduler This article is currently under edit and will be made publish in the near future Keywords BEGIN DBMS_SCHEDULER.CREATE_JOB ( JOB_NAME => 'MY_MV_REFRESH', JOB_TYPE => 'PLSQL_BLOCK', JOB_ACTION => 'BEGIN mv_refresh_job(''EXEC DBMS_MVIEW.REFRESH(''MV_NAME_HERE'', ''C'')''); END;', START_DATE => SYSTIMESTAMP, REPEAT_INTERVAL => 'freq=daily; byhour=0; byminute=0; bysecond=0;', END_DATE => NULL, ENABLED => TRUE, COMMENTS => 'Refreshes Materialized Views on a Scheduled Basis (12:00AM each day).'); END; / SELECT job_name, program_name FROM dba_scheduler_jobs WHERE job_style='LIGHTWEIGHT'; begin dbms_scheduler.create_job(job_name => 'ENGINE_JOB_SCHEDULE' ,job_type => 'PLSQL_BLOCK' ,job_action => 'begin dbms_stats.gather_schema_stats(ownname =>"SCOTT", estimate_percent => 100 , cascade=>true,method_opt=> "for all columns size 1" ,degree => 5 , options => "GATHER STALE"); end;' ,start_date => trunc(sysdate + 1) + 6/24 ,repeat_interval => 'FREQ=DAILY' ,enabled => TRUE ,comments => 'customized stats gathering for risk engine'); end; SQL> begin 2 3 dbms_scheduler.create_job(job_name => 'ENGINE_JOB_SCHEDULE' 4 ,job_type => 'PLSQL_BLOCK' 5 ,job_action => 'begin 6 dbms_stats.gather_schema_stats(ownname =>"SCOTT", estimate_percent => 100 7 , cascade=>true,method_opt=> "for all columns size 1" 8 ,degree => 5 , options => "GATHER STALE"); end;' 9 ,start_date => trunc(sysdate + 1) + 6/24 10 ,repeat_interval => 'FREQ=DAILY' ,enabled => TRUE 11 12 ,comments => 'customized stats gathering for risk engine'); 13 14 end; 15 / PL/SQL procedure successfully completed. grant execute on dbms_scheduler to scott; exec dbms_scheduler.run_job('ENGINE_JOB_SCHEDULE'); set linesize 250 col log_user for a10 col job for 9999999 head 'Job' col broken for a1 head 'B' col failures for 99 head "fail" col last_date for a18 head 'Last|Date' col this_date for a18 head 'This|Date' col next_date for a18 head 'Next|Date' col interval for 9999.000 head 'Run|Interval' col what for a60 select j.log_user, j.job, j.broken, j.failures, j.last_date||':'||j.last_sec last_date, j.this_date||':'||j.this_sec this_date, j.next_date||':'||j.next_sec next_date, j.next_date - j.last_date interval, j.what from (select dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES, dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC, dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT from dba_jobs dj) j; session_jobs.sql set linesize 250 col sid for 9999 head 'Session|ID' col spid head 'O/S|Process|ID' col serial# for 9999999 head 'Session|Serial#' col log_user for a10 col job for 9999999 head 'Job' col broken for a1 head 'B' col failures for 99 head "fail" col last_date for a18 head 'Last|Date' col this_date for a18 head 'This|Date' col next_date for a18 head 'Next|Date' col interval for 9999.000 head 'Run|Interval' col what for a60 select j.sid, s.spid, s.serial#, j.log_user, j.job, j.broken, j.failures, j.last_date||':'||j.last_sec last_date, j.this_date||':'||j.this_sec this_date, j.next_date||':'||j.next_sec next_date, j.next_date - j.last_date interval, j.what from (select djr.SID, dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES, dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC, dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT from dba_jobs dj, dba_jobs_running djr where dj.job = djr.job ) j, (select p.spid, s.sid, s.serial# from v$process p, v$session s where p.addr = s.paddr ) s where j.sid = s.sid; running_jobs.sql set linesize 250 col sid for 9999 head 'Session|ID' col log_user for a10 col job for 9999999 head 'Job' col broken for a1 head 'B' col failures for 99 head "fail" col last_date for a18 head 'Last|Date' col this_date for a18 head 'This|Date' col next_date for a18 head 'Next|Date' col interval for 9999.000 head 'Run|Interval' col what for a60 select j.sid, j.log_user, j.job, j.broken, j.failures, j.last_date||':'||j.last_sec last_date, j.this_date||':'||j.this_sec this_date, j.next_date||':'||j.next_sec next_date, j.next_date - j.last_date interval, j.what from (select djr.SID, dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES, dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC, dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT from dba_jobs dj, dba_jobs_running djr where dj.job = djr.job ) j; exec dbms_scheduler.drop_job('ENGINE_JOB_SCHEDULE'); SQL> exec dbms_scheduler.drop_job('ENGINE_JOB_SCHEDULE'); PL/SQL procedure successfully completed. 'begin dbms_stats.gather_schema_stats(ownname =>'''SCOTT''', estimate_percent => 100 , cascade=>true,method_opt=> '''for all columns size 1''' ,degree => 5 , options => '''GATHER STALE'''); end;' To show details on job run: select log_date , job_name , status , req_start_date , actual_start_date , run_duration from dba_scheduler_job_run_details To show running jobs: select job_name , session_id , running_instance , elapsed_time , cpu_used from dba_scheduler_running_jobs; To show job history: select log_date , job_name , status from dba_scheduler_job_log; show all schedules: select schedule_name, schedule_type, start_date, repeat_interval from dba_scheduler_schedules; show all jobs and their attributes: select * from dba_scheduler_jobs show all program-objects and their attributes select * from dba_scheduler_programs; show all program-arguments: select * from dba_scheduler_program_args; select 'exec dbms_scheduler.disable(' || ''''||OWNER || '.' || job_name || '''' || ');' Disable_Below_Jobs from dba_scheduler_jobs where STATE='RUNNING' and owner ='SYS';CREATE TABLE reminders(message VARCHAR2(4000),msg_ts TIMESTAMP ); DECLARE l_job_action VARCHAR2(500) := 'BEGIN ' ||'INSERT INTO reminders( message, msg_ts) ' ||'VALUES(''What time do you call this ?'', SYSTIMESTAMP); ' ||'END;'; BEGIN DBMS_SCHEDULER.CREATE_JOB( job_name => 'wakeup_sun', job_type => 'PLSQL_BLOCK', job_action => l_job_action, start_date => SYSDATE, repeat_interval => 'freq=weekly; byday=SUN; byhour=8; byminute=30', end_date => NULL, enabled => TRUE, comments => 'Do something automatically on Sunday morning'); END; / BEGIN DBMS_SCHEDULER.RUN_JOB(job_name => 'wakeup_sun'); END; / SELECT * FROM reminders; DECLARE l_job_action VARCHAR2(500) := 'BEGIN ' ||'INSERT INTO reminders( message, msg_ts) ' ||'VALUES(''What time do you call this ?'', SYSTIMESTAMP); ' ||'END;'; BEGIN DBMS_SCHEDULER.CREATE_JOB( job_name => 'wakeup_sun', job_type => 'PLSQL_BLOCK', job_action => l_job_action, start_date => SYSDATE, repeat_interval => 'freq=weekly; byday=SUN; byhour=8; byminute=30', end_date => NULL, enabled => TRUE, comments => 'Do something automatically on Sunday morning'); END; / begin dbms_scheduler.create_job(job_name => 'ENGINE_JOB_SCHEDULE1' ,job_type => 'PLSQL_BLOCK' ,job_action => 'begin dbms_stats.gather_schema_stats(ownname =>''SCOTT'', estimate_percent => 100 , cascade=>true,method_opt=> ''for all columns size 1'' ,degree => 5 , options => ''GATHER STALE''); end;' ,start_date => trunc(sysdate + 1) + 6/24 ,repeat_interval => 'FREQ=DAILY' ,enabled => TRUE ,comments => 'customized stats gathering for risk engine'); end; / BEGIN DBMS_SCHEDULER.RUN_JOB(job_name => 'ENGINE_JOB_SCHEDULE1'); END; / BEGIN DBMS_SCHEDULER.RUN_JOB(job_name => 'ENGINE_JOB_SCHEDULE1'); END; / PL/SQL procedure successfully completed. begin dbms_scheduler.create_schedule( schedule_name => 'DEMO_SCHEDULE' , start_date => '01/01/2006 22:00:00' , repeat_interval => 'FREQ=WEEKLY' , comments => 'Weekly at 22:00'); END; To drop the schedule: begin dbms_scheduler.drop_schedule( schedule_name => 'DEMO_SCHEDULE' , force => TRUE ); end; / begin dbms_scheduler.create_job( job_name => 'DEMO_JOB1' , program_name =>'DEMO_JOB_SCHEDULE' , schedule_name =>'DEMO_SCHEDULE' , enabled => FALSE , comments => 'Run demo program every week at 22:00'); dbms_scheduler.set_job_argument_value( job_name => 'DEMO_JOB1' , argument_position => 1 , argument_value => 'param1'); dbms_scheduler.enable('DEMO_JOB1'); commit; end; / begin dbms_scheduler.create_job ( job_name => 'RUN_SHELL1', schedule_name => 'DEMO_SCHEDULE', job_type => 'EXECUTABLE', job_action => '/home/test/run_script.sh', enabled => true, comments => 'Run shell-script' ); end; / Create a Job /* Run this job every 5 minutes except between 11am and 5pm) */ begin dbms_scheduler.create_job ( job_name => 'test_job_01', job_type => 'PLSQL_BLOCK', job_action => 'BEGIN update test1 set col1 = col1 + 1; END;', start_date => SYSTIMESTAMP, repeat_interval => 'freq=minutely; byhour=17,18,19,20,21,22,23,00,01,02,03,04,05,06,07,08,09,10; interval=5', end_date => NULL, enabled => TRUE, comments => 'Job to test use of dbms_scheduler'); end; /
Search This Blog
Total Pageviews
Sunday, 4 March 2012
dbms_scheduler example
Subscribe to:
Post Comments (Atom)
Oracle DBA
anuj blog Archive
- ► 2011 (362)
No comments:
Post a Comment