Using DBMS_JOB to submit a Job

Syntax of Using DBMS_JOB to Submit a Job

The difinition of the DBMS_JOB.SUMIT procedure is as follows:

DBMS_JOB.SUBMIT(
job OUT BINARY_INTEGER,
what IN VARCHAR2,
next_date IN DATE DEFAULTSYSDATE,
interval IN VARCHAR2 DEFAULT 'NULL',
no_parse IN BOOLEAN DEFAULT FALSE,
instance IN BINARY_INTEGER DEFAULT ANY_INSTANCE,
force IN BOOLEAN DEFAULT FALSE);


We can find an example of using this procedure in the script spauto.sql


dbms_job.submit(
:jobno,
'statspack.snap;',
trunc(sysdate+1/24,'HH'),
'trunc(SYSDATE+1/24,''HH'')',
TRUE,
:instno);



Automate the collection of STATPACK statistics using dbms_job
(1) check job_queue_processes paramter, it should be > 0
(2) Execute the sqlplus script ( collect every 15 min)


Viewing job information from the following views:
DBA_JOBS
DBA_JOBS_RUNNING
USR_JOBS


This procedure changes how often a job runs:

DBMS_JOB.INTERVAL (
job IN BINARY_INTEGER,
interval IN VARCHAR2);



e.g.
execute dbms_job.interval(21, 'sysdate+15/1440')