Chapter 25. DBMS_JOB Built-in Package




You need DBMS_JOB to schedule and execute PL/SQL procedures at predefined times or at regular intervals, such as routine maintenance procedures or developer batch jobs.



The three INIT.ORA parameters that affect DBMS_JOB are:


Specifies (in seconds) how often the Oracle SNP process “wakes up” to check the job queue. Too small an interval (or too many queue processes) decreases server performance; too large an interval can cause the jobs to fall behind schedule. The recommended value for most cases is 60 seconds.


Specifies the number of concurrent SNP background processes (i.e., how many jobs can execute in parallel). This value can range from 0 (the default) to 36. A value of 1 or 2 is usually enough for a small number of jobs.


Specifies whether database connections established by the background processes are held open during “sleep periods” (no jobs running). This value may be either TRUE, in which case the process keeps its connection, or FALSE (the default), in which case the process closes the connection and reestablishes a new one the next time it runs. While a TRUE value yields better performance, it can also interfere with the ability to shut down the database.



Since only one concurrent process is allowed, the second job must wait until the first job is complete.



No, it’s not possible. Every started job establishes a lock in the V$LOCK ...

Get Oracle PL/SQL Programming: A Developer's Workbook now with the O’Reilly learning platform.

O’Reilly members experience live online training, plus books, videos, and digital content from nearly 200 publishers.