Oracle Scheduling Data Dictionary Views Oracle Tips by Burleson Consulting

Data Dictionary Views Related to dbms_job

There are two main dba_% views that relate directly to jobs scheduled via the dba_jobs package.  The following script lists them and their dictionary comments:

* table_comments.sql

-- Parameters:
--    1) Specific USERNAME or ALL which doesn't limit output.
--    2) Table or view name. Partial matches are allowed.
-- *****************************************************************

set verify off
column table_name format a20
column comments   format a50

select
   table_name,
   comments
from
   dba_tab_comments
where
   owner = upper('&1')
and
   table_name like upper('&2%')
order by
   table_name
;

The output displayed by the table_comments.sql script is listed below.

SQL> @table_comments.sql sys dba_jobs

TABLE_NAME           COMMENTS
-------------------- -----------------------------------------------
DBA_JOBS             All jobs in the database
DBA_JOBS_RUNNING     All jobs in the database which are currently running,
                     join v$lock and job

2 rows selected.

As with the majority of dba_% views, there are equivalent all_% and user_% views.  The all_% views display all objects the user owns or has privileges on, while the user_% views display only those objects the user owns.  For the remainder of this topic, the focus will only be on the dba_% views as these views display the full list of objects, regardless of ownership or privileges.

For detailed examples of the proper usage of views, refer to Chapter 5 Monitoring Oracle Job Execution.

dba_jobs

The dba_jobs view is used to display all information related to the database jobs.  This view is described below.

SQL> describe dba_jobs

 Name                          Null?    Type
 ----------------------------- -------- ----------------
 JOB                           NOT NULL NUMBER
 LOG_USER                      NOT NULL VARCHAR2(30)
 PRIV_USER                     NOT NULL VARCHAR2(30)
 SCHEMA_USER                   NOT NULL VARCHAR2(30)
 LAST_DATE                              DATE
 LAST_SEC                               VARCHAR2(8)
 THIS_DATE                              DATE
 THIS_SEC                               VARCHAR2(8)
 NEXT_DATE                     NOT NULL DATE
 NEXT_SEC                               VARCHAR2(8)
 TOTAL_TIME                             NUMBER
 BROKEN                                 VARCHAR2(1)
 INTERVAL                      NOT NULL VARCHAR2(200)
 FAILURES                               NUMBER
 WHAT                                   VARCHAR2(4000)
 NLS_ENV                                VARCHAR2(4000)
 MISC_ENV                               RAW(32)
 INSTANCE                               NUMBER

The usage of the individual columns is explained by their dictionary comments. The following script can be used to display the column comments.

* column_comments.sql

-- *************************************************
-- Copyright ? 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties.  Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************

-- Parameters:
--    1) Specific USERNAME or ALL which doesn't limit output.
--    2) Table or view name.
-- *****************************************************************

set verify off
set pagesize 100
column column_name format a20
column comments    format a50

select
   column_name,
   comments
from
   dba_col_comments
where
   owner = upper('&1')
and
   table_name like upper('&2')
order by
   column_name
;

The output generated from the column_comments.sql script is displayed below.

SQL> @column_comments sys dba_jobs

COLUMN_NAME   COMMENTS
------------- --------------------------------------------------
BROKEN        If Y, no attempt is being made to run this job.
              See dbms_jobq.broken(job).

FAILURES      How many times has this job started and failed since its last success?

INSTANCE      Instance number restricted to run the job

INTERVAL      A date function, evaluated at the start of execution, becomes next NEXT_DATE

JOB           Identifier of job.  Neither import/export nor repeated executions change               it.

LAST_DATE     Date that this job last successfully executed

LAST_SEC      Same as LAST_DATE.  This is when the last successful execution started.

LOG_USER      USER who was logged in when the job was submitted

MISC_ENV      a versioned raw maintained by the kernel, for other session parameters

NEXT_DATE     Date that this job will next be executed

NEXT_SEC      Same as NEXT_DATE.  The job becomes due for execution at this time.

NLS_ENV       alter session parameters describing the NLS environment of the job

PRIV_USER     USER whose default privileges apply to this job

SCHEMA_USER   select * from bar  means  select * from schema_user.bar

THIS_DATE     Date that this job started executing (usually nullif not executing)

THIS_SEC      Same as THIS_DATE.  This is when the last successful execution started.

TOTAL_TIME    Total wallclock time spent by the system on this job, in seconds

WHAT          Body of the anonymous PL/SQL block that this job executes

18 rows selected.

dba_jobs_running

The dba_jobs_running view lists information about all currently running jobs. This view is described below.

SQL> describe dba_jobs_running

 Name                    Null?    Type
 ----------------------- -------- ----
 SID                              NUMBER
 JOB                              NUMBER
 FAILURES                         NUMBER
 LAST_DATE                        DATE
 LAST_SEC                         VARCHAR2(8)
 THIS_DATE                        DATE
 THIS_SEC                         VARCHAR2(8)
 INSTANCE                         NUMBER

The usage of the individual columns is explained by their dictionary comments, which can be displayed using the column_comments.sql script.

SQL> @ column_comments sys dba_jobs_running

COLUMN_NAME          COMMENTS
-------------------- -----------------------------------------------
FAILURES             How many times has this job started and failed                                       since its last success?

INSTANCE             The instance number restricted to run the job

JOB                  Identifier of job.  This job is currently executing.

LAST_DATE            Date that this job last successfully executed

LAST_SEC             Same as LAST_DATE.  This is when the last successful execution                      started.

SID                  Identifier of process which is executing the job See v$lock.

THIS_DATE            Date that this job started executing (usually null if not                      executing)

THIS_SEC             Same as THIS_DATE.  This is when the last successful execution                      started.

8 rows selected.

Now that two data dictionary views have been explained, it is appropriate to review the new scheduling functionality available in Oracle 10g.

Overview of dbms_scheduler Functions

It quickly becomes apparent why the dbms_scheduler package is the recommended way to schedule jobs in Oracle10g.  The dbms_job package is still present in 10g, but only for backward compatibility.  The jobs created using the dbms_job package were very much stand-alone in nature in that they were defined with their own schedules and actions.  In addition, the dbms_scheduler package allows the user to define standard programs and schedules, which can be used by many jobs.

Support for the scheduler is built into the Oracle Enterprise Manager 10g Database Control (OEM 10g DB Control).  The majority of the scheduler objects can be managed via links from the administration page.  Figure 2.3 shows the administration page with the scheduler links on the right hand side towards the bottom of the screen.

Figure 2.3 ? OEM 10g DB Control: Administration

Job classes, windows and window groups provide a link between the scheduler and the resource manager, allowing jobs to run with a variety of resource profiles. They are considered part of the scheduler administration and require the MANAGE SCHEDULER privilege. Specific details about the resource manager is beyond the scope of this chapter, so the sections that deal with administration objects will focus on how to create each type of object, rather than how they should be used.

Before defining objects, it is worth taking a closer look at the privileges necessary to access the scheduler.

 

This is an excerpt from the book "Oracle Job Scheduling" by Dr. Tim Hall. You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle job scheduling scripts.