Oracle DBA: How to schedule jobs in Oracle Database



Last updated on August 31st, 2016 at 05:09 pm

We often wants to run pre-defined jobs at different schedule. Oracle database provides dba_jobs/dbms_scheduler (Oracle Database job scheduler) for the scheduling of 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 greater then 0

3)Is the job BROKEN?
select job,broken from dba_jobs where job=;
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)

With Oracle 10g ,dbms_job has been replaced dbms_scheduler .The Scheduler offers far more functionality than the DBMS_JOB package, which was the previous Oracle Database job scheduler.Oracle recommend 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”, ”TECH.CAT@tech.com”, 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”, ”TECH.CAT@tech.com”, 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;


Leave a Reply