DBMS_SCHEDULER is a newer, more complex job scheduling engine released in 10g, intended to replace DBMS_JOB going forward. DBMS_JOB, the older job scheduler, as of now is still available for quick and easy DB based job scheduling.
Job scheduling comprises the core of the functionality of DBMS_SCHUDULER, however here are some additional benefits available to DBMS_SCHEDULER:
- Logging of job runs (job history)
- Simple but powerful scheduling syntax (similar to but more powerful than cron syntax)
- Running of jobs outside of the database on the operating system (see below)
- Resource management between different classes of jobs
- Use of job arguments including passing of objects into stored procedures
- Privilege-based security model for jobs
- Naming of jobs and comments in jobs
- Stored, reusable schedules
Now, let’s match up similar functionality across both engines.
Create a Job (PL/SQL or DB jobs)
DBMS_SCHEDULER
BEGIN DBMS_SCHEDULER.create_job ( job_name => 'test_full_job_definition', job_type => 'PLSQL_BLOCK', job_action => 'BEGIN DBMS_STATS.gather_schema_stats(''SCOTT''); END;', start_date => SYSTIMESTAMP, repeat_interval => 'freq=hourly; byminute=0', end_date => NULL, enabled => TRUE, comments => 'Job defined entirely by the CREATE JOB procedure.'); END; |
DBMS_JOB
DECLARE l_job NUMBER := 0; BEGIN DBMS_JOB.SUBMIT(l_job,'procedure_name;',sysdate,'TRUNC(sysdate)+1+1/288'); END; |
Run a Job
DBMS_SCHUDULER
EXEC dbms_scheduler.run_job('myjob'); |
DBMS_JOB
EXEC dbms_job.run(job_no); |
Monitor Jobs
DBMS_SCHEDULER
SELECT * FROM dba_scheduler_jobs WHERE job_name = 'MY_JAVA_JOB'; |
DBMS_JOB
SELECT job, what, next_date, next_sec FROM user_jobs; |
Remove a Job
DBMS_SCHEDULER
EXEC DBMS_SCHEDULER.DROP_JOB('my_java_job'); |
DBMS_JOB
EXECUTE DBMS_JOB.REMOVE(jobno); |
DBMS_SCHEDULER Functionality
DBMS_SCHEDULER has the ability to schedule a job to run outside of the DB. The following will run the myClass java executable every minute.
BEGIN DBMS_SCHEDULER.CREATE_JOB ( job_name => 'my_java_job', job_type => 'EXECUTABLE', job_action => '/usr/bin/java myClass', repeat_interval => 'FREQ=MINUTELY', enabled => TRUE ); END; |
REPEAT_INTERVAL Examples
'FREQ=DAILY; BYDAY=SUN; BYHOUR=18;', 'freq=MINUTELY;interval=1', 'freq=MINUTELY;interval=5', 'freq=HOURLY;interval=1', 'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI,SAT,SUN; BYHOUR=22;', |
Now you want to know if you have job in running?
No problem! there is already a post with the query to execute for databases oracle in single and Rac (Real Application Cluster) instance:
http://clanxii.altervista.org/blog/oracle1/