Home » Oracle » Oracle Database » How to schedule jobs in Oracle Database: DBA_JOBS, DBA_SCHEDULER

How to schedule jobs in Oracle Database: DBA_JOBS, DBA_SCHEDULER

Introduction

We often want to run pre-defined jobs at different schedules. Oracle database provides dba_jobs/dbms_scheduler (Oracle Database job scheduler) for scheduling pre-defined jobs.

Information about DBA_JOBS ( Till 9i)

Query to see the jobs running through dba_jobs

select /*+ rule */ * from dba_jobs_running;

or

select /*+ ordered */ * from dba_jobs_running;

Common Reason why jobs don’t execute automatically and as scheduled
(1) select instance_name,logins from v$instance;
If the logins=RESTRICTED, then:

alter system disable restricted session;

(2) check the JOB_QUEUE_PROCESSES

show parameter JOB_QUEUE_PROCESSES

It should be greater than 0

(3)Is the job BROKEN?

select job, broken from dba_jobs where job=&1;
How to set to Broken to N
exec dbms_job.broken(job_no,FALSE);
How to set to Broken to Y
exec dbms_job.broken(job_no,TRUE);

If broken, then check the alert log and trace files to diagnose the issue

(4) _SYSTEM_TRIG_ENABLED=FALSE
Check if _system_enabled_trigger=false

col parameter format a25
col value format a15
select a.ksppinm parameter,b.ksppstvl value from x$ksppi a,x$ksppcv b
Where a.indx=b.indx and ksppinm=’_system_trig_enabled’;

Information about DBA_SCHEDULER(With 10g and above)

With Oracle 10g,dbms_job has been replaced with dbms_scheduler. The Scheduler offers far more functionality than the DBMS_JOB package, which was the previous Oracle Database job scheduler. Oracle recommends changing all the jobs from DBA_JOBS to DBA_SCHEDULER from 10g onwards

— Old using dbms_job scheduler.

VARIABLE jobno NUMBER;
 BEGINDBMS_JOB.SUBMIT(:jobno, 'INSERT INTO empl VALUES (111, ''TECH'',''CAT'', ''[email protected]'', NULL, SYSDATE, ''AD_PRES'', NULL,NULL, NULL, NULL);', SYSDATE, 'SYSDATE+1');
 COMMIT;
 END;
 /
 PRINT l_job

New with dbms_scheduler scheduler.

BEGIN
 DBMS_SCHEDULER.CREATE_JOB(
 job_name          =>  'job1', 
 job_type          =>  'PLSQL_BLOCK',
 job_action        =>  'INSERT INTO empl VALUES (111, ''TECH'',''CAT'', ''[email protected]'', NULL, SYSDATE, ''AD_PRES'', NULL,NULL, NULL, NULL);
 start_date        =>  SYSDATE,
 repeat_interval   =>  'FREQ = DAILY; INTERVAL = 1');
 END;
 /

We can find job information by running the below queries

select 
 job_name,
 enabled
 from
 user_scheduler_jobs;

 select
 job_id,
 freq_type,
 freq_interval,
 freq_subday_type,
 freq_subday_interval, 
 freq_relative_interval,
 freq_recurrence_factor,
 active_start_date,
 active_end_date,
 active_start_time,
 active_end_time,
 schedule_id
 from
 dba_scheduler_jobs;

 SELECT owner, job_name, enabled FROM dba_scheduler_jobs; 

The job created above can be dropped using the below command

BEGIN
   DBMS_SCHEDULER.drop_job (job_name => 'job1');
 END;
 /

The job can be enabled and disabled using the below command

execute dbms_scheduler.disable(job_name => 'job1');
execute dbms_scheduler.enable(job_name => 'job1');

The above is a straightforward use of DBMS_SCHEDULER. DBMS_SCHEDULER has a lot of other features also like creating a program, and creating a schedule which then can be used to create Jobs for scheduling

See also  Script to Update a profile for Multiple Users in EBS

DBMS_JOB Jobs Converted to DBMS_SCHEDULER Jobs in Oracle Database 19c

  • Starting with Oracle 19c, Any jobs created using the DBMS_JOB package are implemented as DBMS_SCHEDULER jobs. The internal procedures have been changed. Your calls will work the same way, but DBMS_JOB is now a legacy interface to the DBMS_SCHEDULER.
  • During upgrades to 19c, any jobs defined using DBMS_JOB also get converted to DBMS_SCHEDULER jobs
  • Oracle has created a new dictionary table scheduler$_dbmsjob_map for mapping purposes
  • Before Oracle 19c, DBMS_JOB functioned separately from DBMS_SCHEDULER, allowing users to utilize either tool for their needs. Starting with Oracle 19c, however, DBMS_JOB has been integrated with DBMS_SCHEDULER, effectively making DBMS_JOB a subset or interface for DBMS_SCHEDULER. This integration means that when creating a job via DBMS_JOB in Oracle 19c and beyond, it is, in reality, being created through DBMS_SCHEDULER. Consequently, the use of DBMS_JOB now requires the CREATE JOB permission, reflecting its reliance on DBMS_SCHEDULER for job execution.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top