How to submit a new job using dbms_job?

Using dbms_job one can schedule a job, in the following example schedules a job to run every 5 minutes starting from current time. The next interval can be fixed time or it can be based on the time when the last run finished, in the example below the interval is time when the last run/time when the job finishes so the next run would occur 5 minutes after the last run. The owner, priv user and log user of the job created would be the user who executed the dbms_job.submit.
SQL> set serveroutput on
SQL> declare
job_id number;
dbms_job.submit(job_id, 'dbms_lock.sleep(100);', sysdate, 'sysdate+5/(24*60)');
dbms_output.put_line('Job: ' || job);
Job: 21
PL/SQL procedure successfully completed.

--Make sure you run commit to schedule the job.
SQL> commit;

-- To see the time when the job last run and next time it will run
SQL> select job, last_date, last_sec, next_date, next_Sec from dba_jobs where job in (21);

———- ——— ——– ——— ——–
21 19-FEB-12 19:57:43 19-FEB-12 20:02:43

Leave a Reply