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

Intermediate

Q:

25-24.

When the job execution fails, Oracle attempts to start it again one minute later. If it fails again, Oracle repeatedly doubles the amount of time it waits before the next execution attempt (i.e., 2, 4, 8, 16, etc., minutes), until it either exceeds the normal execution interval or fails 16 times. In this case, the job is flagged as broken.

Q:

25-25.

The actual sequence is as follows:

  1. Start database session with job owner’s username.

  2. Alter session NLS settings to those settings that were active when job was submitted.

  3. Calculate next execution date using interval’s formula.

  4. Execute PL/SQL mentioned in the “what” parameter.

  5. If block fails, increment failures number, set next execution date to one minute later if it’s the first failure, double the interval otherwise.

  6. If there are no errors, update next_date.

Tip

Oracle always alters the session’s NLS parameters to the ones used when the job was originally submitted. For example, if you change the NLS_DATE_FORMAT after the job was submitted, that job still uses the original NLS_DATE_FORMAT defined when you first submitted the job.

Q:

25-26.

Since jobs running as background processes have no associated terminal, it’s impossible to put an error message to the terminal. Consequently, to find an error that occurred during execution, you must check the log files to find the error number(s) and message(s).

To do this, check either the RDBMSxx/TRACE directory in the Oracle home catalog or the path defined in the BACKGROUND_DUMP_DEST parameter ...

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