Chapter 25. DBMS_JOB Built-in Package

Beginner

Q:

25-1.

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.

Q:

25-2.

The three INIT.ORA parameters that affect DBMS_JOB are:

JOB_QUEUE_INTERVAL

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.

JOB_QUEUE_PROCESSES

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.

JOB_QUEUE_KEEP_CONNECTIONS

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.

Q:

25-3.

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

Q:

25-4.

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

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

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