O'Reilly logo

Oracle PL/SQL Programming: A Developer's Workbook by Andrew Odewahn, Steven Feuerstein

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required