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

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.