DBMS_SCHEDULER create schedule create job

- create schedule

 -- Creating a common schedule which could be used to schedule a JOB to run Daily at 1:00 AM
     -- US/Eastern Time.
DBMS_SCHEDULER.create_schedule(
                                    schedule_name   => 'DLY0100_COMMON_SCHED'                     ,
                                    start_date      =>  trunc(SYSDATE+1) at TIME ZONE 'US/Eastern',
                                    repeat_interval => 'freq=daily; byhour=1;'                    ,
                                    comments        => 'Runtime - Everyday (Mon-Sun) at 01:00 AM US/Eastern Time'
                                   );


 --
     -- Create a job with an inline program and a defined schedule to
     -- do daily purge for g  tables.
     --
     DBMS_SCHEDULER.create_job(
                               job_name      => 'DLY0100_DAILY_PURGE_GRP1'         ,
                               schedule_name => 'DLY0100_COMMON_SCHED'             ,
                               job_type      => 'PLSQL_BLOCK'                      ,
                               job_action    => 'BEGIN daily_purge(null,1); END;'  ,
                               enabled       => TRUE                               ,
                               auto_drop     => FALSE                              ,
                               comments      => 'Daily job to purge group 1 tables.'
                              );

 -- Creating a common schedule which could be used to schedule a JOB to run Weekly at 3:00 AM
     -- US/Central Time.
     --
     DBMS_SCHEDULER.create_schedule(
                                    schedule_name   => 'WKLY0300_COMMON_SCHED'                     ,
                                    start_date      =>  trunc(SYSDATE+1) at TIME ZONE 'US/Eastern',
                                    repeat_interval => 'freq=weekly; byday=sun; byhour=2;'                    ,
                                    comments        => 'Runtime - Every Sun at 3:00AM '
                                   );

-- Create a job with  proecdure
   DBMS_SCHEDULER.create_job(
                               job_name      => 'WKLY0300_SP_COLLECT_MIS_STATS'       ,
                               schedule_name => 'WKLY0300_COMMON_SCHED'              ,
                               job_type      => 'STORED_PROCEDURE'                       ,
                               job_action    => 'sp_collect_mis_stats',
                               enabled       => TRUE                                ,
                               auto_drop     => FALSE                               ,
                               comments      => 'Weekly job to collect mis stats'
                              );

Scheduler disable/enable

exec dbms_scheduler.drop_job('SCOTT.TESTJOB');
exec dbms_scheduler.disable('TESTJOB');
exec dbms_scheduler.enable('"TESTJOB"');
exec dbms_scheduler.stop_job('TESTJOB');

Scheduler Jobs report

SPOOL verify_scheduler_changes.log
--
SELECT to_char(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') "START_TIME"
  FROM DUAL;
--
PROMPT '*****************************************************************';
--
DEFINE TEST='CHECK Scheduler Jobs Creation and various status';
--
PROMPT 'Verify:    &TEST'
PROMPT 'User:      &_USER'
PROMPT 'Database:  &_CONNECT_IDENTIFIER';
PROMPT '**** CHECK Scheduler Jobs Creation and various status Report ****';
PROMPT '*****************************************************************';
--
SET linesize 157;
--
PROMPT ' '
PROMPT  '**** Query Number 1 ****'
PROMPT ' 1) The below query is used to check following things:'
PROMPT ' '
--
PROMPT '   a) Whether the scheduler jobs have been created successfully.'
PROMPT '   b) All the jobs should be in enabled state i.e. ENABLED=TRUE. If it is not then the job would probably not run on the schedule date/time'
PROMPT '   c) Check the state column, it should be 'SCHEDULED''
PROMPT '   d) Check the last_start_date, the next_run_date to verify whether the job ran at the scheduled time.'
PROMPT '   e) Check the failure_count, ideally it should be zero. If not then use query # 2 or 3 to check why the job failed.'
PROMPT '   f) Check the run_count column to see how many times the job ran.'

--
  SELECT  job_name
         ,enabled
         ,state
         ,run_count
         ,failure_count
         ,rpad(last_start_date,39) AS "LAST_START_DATE"
         ,rpad(next_run_date,39)   AS "NEXT_RUN_DATE"
    FROM  user_scheduler_jobs
ORDER BY  job_name;
--
PROMPT ' '
PROMPT ' '
PROMPT  '**** Query Number 2 ****'
PROMPT ' 1) The below query is used to check following things:'
PROMPT ' '
--
PROMPT '   a) Whether each job ran at the requested scheduled time.'
PROMPT '   b) Check the actual start date.'
PROMPT ' '
--
COLUMN job_name FORMAT A30
COLUMN status FORMAT A12
--
  SELECT  to_char(log_date, 'DD-MON-YY HH24:MI:SS') AS "LOG_DATE"
         ,job_name
         ,status
         ,to_char(req_start_date, 'DD-MON-YY HH24:MI:SS') AS "REQ_START_DATE"
         ,to_char(actual_start_date, 'DD-MON-YY HH24:MI:SS') AS "ACTUAL_START_DATE"
         ,rpad(run_duration,25) AS "JOB_RUN_DURATION"
    FROM  user_scheduler_job_run_details
ORDER BY  log_date DESC;
--
--
PROMPT ' '
PROMPT ' '
PROMPT  '**** Query Number 3 ****'
PROMPT ' 1) The below query is used to check following things:'
PROMPT ' '
--
PROMPT '   a) How many jobs are in failed or broken status.'
PROMPT '   b) Check the additional_info column to check why the job failed'
PROMPT ' '
--
COLUMN job_name FORMAT A30
COLUMN status FORMAT A12
COLUMN additional_info word_wrapped
--
  SELECT  to_char(log_date, 'DD-MON-YY HH24:MI:SS') AS "LOG_DATE"
         ,job_name
         ,status
         ,additional_info
    FROM  user_scheduler_job_run_details
   WHERE  status != 'SUCCEEDED'
ORDER BY  log_date DESC;
--
--
--
PROMPT ' '
PROMPT ' '
PROMPT  '**** Query Number 4 ****'
PROMPT ' 1) The below query is used to check following things:'
PROMPT ' '
--
PROMPT '   a) Whether all the schedules have been created.'
PROMPT '   b) Start date of each schedule should match with the create scripts.'
PROMPT ' '
--
COLUMN schedule_name FORMAT A30
COLUMN repeat_interval FORMAT A50
COLUMN comments FORMAT A55 word_wrapped

  SELECT  schedule_name
         ,to_char(start_date, 'DD-MON-YY HH24:MI:SS') AS "START_DATE"
         ,repeat_interval
         ,comments
    FROM  user_scheduler_schedules
ORDER BY  schedule_name;
--
--
--
PROMPT ' '
PROMPT ' '
PROMPT  '**** Query Number 5 ****'
PROMPT ' 1) The below query is used to check following things:'
PROMPT ' '
--
PROMPT '   a) Whether all the programs have been created.'
PROMPT '   b) Program action for each program, this tells us what package/procedure is been referenced.'
PROMPT '   c) All the programs should be in Enables state'
PROMPT ' '
--
COLUMN program_name FORMAT A30
COLUMN program_action FORMAT A60 word_wrapped
COLUMN comments FORMAT A55 word_wrapped
--
  SELECT  program_name
         ,program_action
         ,enabled
         ,comments
    FROM  user_scheduler_programs
ORDER BY  program_name;
--
--
--
PROMPT ' '
PROMPT ' '
PROMPT  '**** Query Number 6 ****'
PROMPT ' 1) The below query is used to check following things:'
PROMPT ' '
--
PROMPT '   a) This query will show all the programs which are not in enabled state. Ideally you should see no rows.'
PROMPT '   b) All the programs should be in Enables state'
PROMPT ' '
--
  SELECT  program_name
         ,program_action
         ,enabled
         ,comments
    FROM  user_scheduler_programs
   WHERE  enabled != 'TRUE'
ORDER BY  program_name;
--
--
--
PROMPT ' '
PROMPT ' '
PROMPT  '**** Query Number 7 ****'
PROMPT ' 1) The below query is used to check following things:'
PROMPT ' '
--
PROMPT '   a) How many dbms_jobs are still running and their function as defined in the "what column"'
PROMPT '   b) Ideally there should not be any dbms_jobs in the critical DB and there should be one dbms_job on the log DB to delete the event log'
PROMPT '      tables. This would be soon removed once the partitioning changes are checked in.'
PROMPT '   c) If you find dbms_jobs in the critical then all of them should be removed by running remove_all_user_jobs.sql (checkd in and part of 9A kit)'
PROMPT '      This script is not part of the rebuild script as it had to be run only once.'
PROMPT '   d) Once the partition maintenance code is part of the kit, then the dbms_job on the log side has to be removed.'
PROMPT ' '
--
COLUMN job FORMAT 9999
COLUMN what word_wrapped
--
SELECT  job
       ,what
  FROM  user_jobs;
--
PROMPT ' '
--
SELECT to_char(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') "END_TIME"
  FROM DUAL;
--
PROMPT '*****  END Of  Report *****'
--
SPOOL OFF;
QUIT