Dbms scheduler jobs, programs and schedules

• Some useful queries

Create a scheduled job

DBMS_SCHEDULER.CREATE_JOB( job_name        => 'rebuild_view_all_rmans'
                         , job_type        => 'STORED_PROCEDURE'
                         , job_action      => 'create_all_rmans.rebuild_view'
                         , repeat_interval => 'FREQ=DAILY;BYHOUR=8;BYMINUTE=0;BYSECOND=0'
                         , enabled         => TRUE
                         , comments        => 'Rebuild view ALL_RMANS');

or create a job to run statspack at 15 minute intervals during office hours

col JOB_NAME      for a20
col OWNER         for a20
col JOB_ACTION    for a30
col START_DATE    for a50
col START_DATE    for a40
col NEXT_RUN_DATE for a40
select OWNER,JOB_NAME,JOB_ACTION,START_DATE,NEXT_RUN_DATE,STATE,ENABLED from dba_scheduler_jobs where job_name like '%STATS%';

set serveroutput on
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
   job_name        => 'statspack_snap'
  ,job_type        => 'STORED_PROCEDURE'
  ,job_action      => 'statspack.snap'
  ,repeat_interval => 'FREQ=HOURLY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=8,9,10,11,12,13,14,15,16,17,18; BYMINUTE=15,30,45'
  ,auto_drop       => FALSE
  ,enabled         => TRUE
  ,comments        => 'statspack snap');
END;

select OWNER,JOB_NAME,JOB_ACTION,START_DATE,NEXT_RUN_DATE,STATE,ENABLED from dba_scheduler_jobs where job_name like '%STATS%';

Run a scheduled job

execute dbms_scheduler.run_job('xxxTOOLS.REBUILD_VIEW_ALL_RMANS');

Examples

BEGIN
  DBMS_SCHEDULER.CREATE_JOB
    (
       job_name        => 'CREDIT_DBA.JOB_DEBUG_SHARED_DBLINK_DUAL'
      ,start_date      => to_date('2021/09/07 11:20:00','yyyy/mm/dd hh24:mi:ss')
      ,repeat_interval => 'FREQ=MINUTELY; INTERVAL=5'
      ,end_date        => NULL
      ,job_class       => 'DEFAULT_JOB_CLASS'
      ,job_type        => 'PLSQL_BLOCK'
      ,job_action      => 'BEGIN credit_dba.debug_shared_dblink_issue.loop_select_dual; END;'
    );
END;
/

BEGIN
 DBMS_SCHEDULER.ENABLE ('CREDIT_DBA.JOB_DEBUG_SHARED_DBLINK_DUAL');
END;
/


BEGIN
 DBMS_SCHEDULER.DISABLE ('CREDIT_DBA.JOB_DEBUG_SHARED_DBLINK_DUAL');
END;
/



select *
from dba_scheduler_jobs
where  job_name like 'JOB_DEBUG_SHARED_DBLINK%'


BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE (
   name         =>  'CREDIT_DBA.JOB_DEBUG_SHARED_DBLINK_DUAL',
   attribute    =>  'repeat_interval',
   value        =>  'FREQ=MINUTELY; INTERVAL=1');
END;
/