Introduction
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=&1;
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 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'', ''[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 above is very simple use of DBMS_SCHEDULER. DBMS_SCHEDULER has lot of other features also like create program , create schedule which then can then use to create the Jobs for scheduling
Leave a Reply