Call (800) 766-1884 for Oracle support & training

Oracle Scheduler Disabled Jobs

Oracle Tips by Burleson Consulting

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan). The following is an excerpt from the book.

The Scheduler Jobs (Disabled) page of OEM, shown in Figure 11.30, lists all currently disabled jobs. This screen is essentially the same as the Scheduler Jobs (Scheduled) screen except the output from the dba_scheduler_jobs view is restricted using the ENABLED column. The disabled_jobs.sql script shows how the disabled jobs can be displayed.

disabled_jobs.sql

set linesize 200

column owner format a15

column next_run_date format a25

select

job_name,

owner,

nvl(to_char(next_run_date, 'DD-MON-YYYY HH24:MI:SS'), schedule_name) as next_run_date,

to_char(last_start_date, 'DD-MON-YYYY HH24:MI:SS') as last_run_date,

job_class,

run_count

from

dba_scheduler_jobs

where

enabled = 'FALSE'

;

If a job is disabled, it will be listed in the output from the disabled_jobs.sql script.

SQL> exec dbms_scheduler.disable ('test_full_job_definition', true);

PL/SQL procedure successfully completed.

SQL> @disabled_jobs

JOB_NAME OWNER NEXT_RUN_DATE LAST_RUN_DATE JOB_CLASS RUN_COUNT

------------------------------ --------------- ------------------------- -------------------- ------------------------------ ----------

TEST_FULL_JOB_DEFINITION JOB_USER 26-JUN-2004 13:42:00 26-JUN-2004 13:36:00 DEFAULT_JOB_CLASS 733

The same information is displayed in the Scheduler Jobs (Disabled) page of OEM shown in Figure 11.30.

Figure 11.30 – OEM DB Control: Scheduler Jobs (Disabled)

The dba_scheduler_job_run_details view provides a history of previous job runs. The job_run_history.sql script uses a top-n query to return a specified number of records from the history for a specified job or all jobs.

job_run_history.sql

-- *****************************************************************

-- Parameters:

-- 1) Specific job name or ALL which doesn't limit output.

-- 2) Number of records to be displayed.

-- *****************************************************************

set linesize 200

set verify off

column owner format a15

column status format a10

column completion_date format a20

column run_duration format a20

select

from

(select

job_name,

owner,

status,

to_char(actual_start_date + run_duration, 'DD-MON-YYYY HH24:MI:SS') as completion_date,

run_duration

from

dba_scheduler_job_run_details

where

job_name = decode(upper('&1'), 'ALL', job_name, upper('&1'))

and

actual_start_date is not null

order by

(actual_start_date + run_duration) DESC) a

where

rownum <= &2

;

The following output lists history information from a specific job and all jobs. The output is restricted to five rows by the second parameter.

SQL> @job_run_history test_sched_job_definition 5

JOB_NAME OWNER STATUS COMPLETION_DATE RUN_DURATION

------------------------------ --------------- ---------- -------------------- ------------

TEST_SCHED_JOB_DEFINITION JOB_USER SUCCEEDED 24-JUN-2004 10:01:59 1

TEST_SCHED_JOB_DEFINITION JOB_USER SUCCEEDED 24-JUN-2004 09:01:59 1

TEST_SCHED_JOB_DEFINITION JOB_USER SUCCEEDED 24-JUN-2004 08:11:21 1

TEST_SCHED_JOB_DEFINITION JOB_USER SUCCEEDED 23-JUN-2004 18:01:59 1

TEST_SCHED_JOB_DEFINITION JOB_USER SUCCEEDED 23-JUN-2004 17:01:59 1

SQL> @job_run_history all 5

JOB_NAME OWNER STATUS COMPLETION_DATE RUN_DURATION

------------------------------ --------------- ---------- -------------------- ------------

TEST_FULL_JOB_DEFINITION JOB_USER SUCCEEDED 24-JUN-2004 10:31:43 1

TEST_FULL_JOB_DEFINITION JOB_USER SUCCEEDED 24-JUN-2004 10:29:43 1

TEST_FULL_JOB_DEFINITION JOB_USER SUCCEEDED 24-JUN-2004 10:27:43 1

TEST_FULL_JOB_DEFINITION JOB_USER SUCCEEDED 24-JUN-2004 10:25:43 1

TEST_FULL_JOB_DEFINITION JOB_USER SUCCEEDED 24-JUN-2004 10:23:43 1

The Scheduler Jobs (Run History) page of OEM, shown in Figure 11.31, lists the full job run history.

Figure 11.31 – OEM DB Control: Scheduler Jobs (Run History)

The scheduled_job_details.sql script displays a summary of the information available for a specific job including a limited job history.

scheduled_job_details.sql

-- Parameters:

-- 1) Specific job name.

-- 2) Number of history records to be displayed.

-- *****************************************************************

set verify off

set feedback off

set linesize 200

column owner format a15

column comments format a50

prompt

prompt GENERAL

prompt --------

select

job_name,

owner,

enabled,

logging_level,

job_class,

comments

from

dba_scheduler_jobs

where

job_name = upper('&1');

column repeat_interval format a40

column start_date format a20

column end_date format a20

column next_run_date format a20

prompt

prompt SCHEDULE

prompt ---------

select

repeat_interval,

to_char(start_date, 'DD-MON-YYYY HH24:MI:SS') as start_date,

to_char(end_date, 'DD-MON-YYYY HH24:MI:SS') as end_date,

to_char(next_run_date, 'DD-MON-YYYY HH24:MI:SS') as next_run_date

from

dba_scheduler_jobs

where

job_name = upper('&1');

column job_action format a100

prompt

prompt COMMAND

prompt ---------

select

job_action

from

dba_scheduler_jobs

where

job_name = upper('&1');

column status format a10

column completion_date format a20

column run_duration format a20

prompt

prompt RUN HISTORY

prompt ------------

select

from

(select

job_name,

owner,

status,

to_char(actual_start_date + run_duration, 'DD-MON-YYYY HH24:MI:SS') as completion_date,

run_duration

from

dba_scheduler_job_run_details

where

job_name = decode(upper('&1'), 'ALL', job_name, upper('&1'))

and

actual_start_date is not null

order by

(actual_start_date + run_duration) DESC) a

where

rownum <= &2

;

set feedback on

An example of the output generated by the script is listed below:

SQL> @scheduled_job_details test_full_job_definition 5

GENERAL

-------

JOB_NAME OWNER ENABL LOGG JOB_CLASS COMMENTS

------------------------------ --------------- ----- ---- ------------------------------ --

TEST_FULL_JOB_DEFINITION JOB_USER TRUE RUNS DEFAULT_JOB_CLASS Job defined entirely by the CREATE JOB procedure.

SCHEDULE

--------

REPEAT_INTERVAL START_DATE END_DATE NEXT_RUN_DATE

---------------------------------------- -------------------- -------------------- --------

FREQ=MINUTELY;INTERVAL=2 23-JUN-2004 09:22:00 24-JUN-2004 10:42:00

COMMAND

--------

JOB_ACTION

-------------------------------------------------------------------------------------------

BEGIN my_job_proc('CREATE_PROGRAM (BLOCK)'); END;

RUN HISTORY

-----------

JOB_NAME OWNER STATUS COMPLETION_DATE RUN_DURATION

------------------------------ --------------- ---------- -------------------- ------------

TEST_FULL_JOB_DEFINITION JOB_USER SUCCEEDED 24-JUN-2004 10:41:43 1

TEST_FULL_JOB_DEFINITION JOB_USER SUCCEEDED 24-JUN-2004 10:39:43 1

TEST_FULL_JOB_DEFINITION JOB_USER SUCCEEDED 24-JUN-2004 10:37:43 1

TEST_FULL_JOB_DEFINITION JOB_USER SUCCEEDED 24-JUN-2004 10:35:43 1

TEST_FULL_JOB_DEFINITION JOB_USER SUCCEEDED 24-JUN-2004 10:33:43 1

Clicking on a job run in the Scheduler Jobs (Run History) page produces the View Job page, shown in Figure 11.32, which contains similar information to the scheduled_job_details.sql script.

Figure 11.32 – OEM DB Control: View Job

Clicking on one of the individual operations in this screen produces the Operation Detail screen, shown in Figure 11.33.

Figure 11.33 – OEM DB Control: Operation Detail

The previous two sections showed how running jobs along with their associated sessions can be identified. The next section will focus on monitoring the individual sessions.

Oracle Consulting

Oracle performance Tuning 10g reference poster

Oracle training & performance tuning books

Oracle® is the registered trademark of Oracle Corporation.