Database Reference
In-Depth Information
The scheduler provides several metadata attributes to jobs of type PLSQL_BLOCK. Oracle10
g
and Oracle11
g
support the same attributes (see page 114-61 in
Oracle Database PL/SQL Packages
and Types Reference 11g Release 1
). Unfortunately, the value of the sequence
SYS.SCHEDULER$_
INSTANCE_S
, which is used for numbering the column
LOG_ID
in the views
DBA_SCHEDULER_
JOB_LOG
and
DBA_SCHEDULER_JOB_RUN_DETAILS
is not among them. It would be very useful for
integrating custom logging in a database table with the scheduler's own logging. Yet, there is
a workaround. After a scheduled job run, the value of
DBA_SCHEDULER_JOBS.NEXT_RUN_DATE
is
copied to
DBA_SCHEDULER_JOB_RUN_DETAILS.REQ_START_DATE
(requested start date). A job of type
PLSQL_BLOCK, which selects
NEXT_RUN_DATE
and incorporates it into a custom logging table,
solves the integration issue. After the job has completed, the custom logging table may be joined
with
DBA_SCHEDULER_JOB_RUN_DETAILS
using the column
REQ_START_DATE
. A sample implemen-
tation is included in the file
rman_backup.sql
in the source code depot.
Removal of Environment Variables
It is undocumented that the scheduler removes all environment variables before it starts a
UNIX process, which implements an external job. Evidence for this trait is easily collected by
having the scheduler run a shell script that calls the program
env
. On UNIX systems, if called
without arguments,
env
prints out all the environment variables that it inherited. Following is
the code of a shell script that prints a sorted list of all environment variables on standard error
output and saves a copy of the output to the file
/tmp/env.out
by using the command
tee
. Execute
permission on the file
env.sh
is required and is set using
chmod
.
$ more env.sh
#!/bin/sh
env | sort | tee /tmp/env.out 1>&2
exit 0
$ chmod +x env.sh
To have the database scheduler run this shell script, we create the following scheduler
program called “ENV”:
SQL> BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM(
program_name=>'env',
program_action=>'/home/oracle/env.sh',
program_type=>'EXECUTABLE',
number_of_arguments=>0,
comments=>'environment variables',
enabled=>true);
end;
/
Then we create a job that uses the preceding program:
SQL> BEGIN
sys.dbms_scheduler.create_job(
job_name => 'env_job',
program_name => 'env',
