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;
begin
dbms_job.submit(job_id, 'dbms_lock.sleep(100);', sysdate, 'sysdate+5/(24*60)');
dbms_output.put_line('Job: ' || job);
end;
/
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);

JOB LAST_DATE LAST_SEC NEXT_DATE NEXT_SEC
———- ——— ——– ——— ——–
21 19-FEB-12 19:57:43 19-FEB-12 20:02:43

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s