How to view Oracle job logs Purge log history job using DBMS_SCHEDULER.PURGE_LOG …
Tuesday, January 1, 2019

How to view Oracle job logs Purge log history job using DBMS_SCHEDULER.PURGE_LOG …


Call now: 252-767-6166 

action=http://www.email-publisher.com/survey/ method=post

target=New_Window>

 

 Home
  E-mail Us

 Oracle

Articles

New Oracle Articles


 Oracle

Training
 Oracle

Tips


 Oracle

Forum
 Class

Catalog


 Remote

DBA
 Oracle

Tuning
 Emergency

911
 RAC

Support
 Apps

Support
 Analysis
 Design
 Implementation
 Oracle

Support


 SQL Tuning
 Security

 Oracle

UNIX
 Oracle

Linux
 Monitoring
 Remote s
upport
 Remote

plans
 Remote

services
 Application

Server

 Applications
 Oracle

Forms
 Oracle

Portal
 App

Upgrades
  SQL

Server
  Oracle Concepts
 Software Support
 Remote

S upport

 

 Development  

 Implementation


 Consulting Staff
 Consulting

Prices
 Help

Wanted!

 

 Oracle Posters
 Oracle Books

 Oracle

Scripts

 Ion

 Excel-DB  

Don Burleson Blog  

 

 

 


 

 

 

 

 

Monitoring Jobs in Oracle 10g

Oracle Tips by Burleson Consulting

Monitoring Jobs in 10g

Chapter 2 noted that Oracle10g includes a new
set of dba_scheduler_% views to display scheduler information.  This
section will examine the information that can be gathered regarding
scheduled jobs using these views and the OEM 10g DB Control.

 

The list of currently scheduled jobs is
displayed using the dba_scheduler_jobs view, which provides list of
job names and the basic schedule information.  The
scheduled_jobs.sql script shows how this view is used.

 

* scheduled_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

;

 

The output generated from the
scheduled_jobs.sql script is shown below.

 

SQL> @scheduled_jobs

 

JOB_NAME                       OWNER          
NEXT_RUN_DATE             LAST_RUN_DATE        JOB_CLASS                      
RUN_COUNT

—————————— —————
————————- ——————–
—————————— ———-

GATHER_STATS_JOB               SYS            
MAINTENANCE_WINDOW_GROUP  24-JUN-2004 08:09:39 AUTO_TASKS_JOB_CLASS                   
4

PURGE_LOG                      SYS            
24-JUN-2004 03:00:00      23-JUN-2004 03:00:01 DEFAULT_JOB_CLASS                     
19

TEST_FULL_JOB_DEFINITION       JOB_USER       
24-JUN-2004 08:52:00      24-JUN-2004 08:52:00 DEFAULT_JOB_CLASS                    
281

TEST_PROG_SCHED_CLASS_JOB_DEF  JOB_USER       
24-JUN-2004 09:00:16      24-JUN-2004 08:09:39 TEST_JOB_CLASS                        
16

ARGUMENT_JOB_DEFINITION        JOB_USER       
24-JUN-2004 09:00:16      24-JUN-2004 08:09:39 DEFAULT_JOB_CLASS                     
16

TEST_SCHED_JOB_DEFINITION      JOB_USER       
24-JUN-2004 09:00:16      24-JUN-2004 08:09:37 DEFAULT_JOB_CLASS                     
16

TEST_PROG_JOB_DEFINITION       JOB_USER       
24-JUN-2004 09:00:09      24-JUN-2004 08:09:38 DEFAULT_JOB_CLASS                     
16

TEST_PROG_SCHED_JOB_DEFINITION JOB_USER       
24-JUN-2004 09:00:16      24-JUN-2004 08:09:39 TEST_JOB_CLASS                        
16

 

8 rows selected.

 

This information is also available from OEM on
the Scheduler Jobs (Scheduled) page (Administration > Jobs) shown in
Figure 5.1.

 

 

Figure 5.1 ? OEM 10g DB Control: Scheduler Jobs
(Scheduled)

 

The dba_scheduler_running_jobs view is the real
starting point for job monitoring as it displays a list of the
currently running jobs.  Using this view, the user is able to
identify the session that is actually executing the job, giving the
ability to monitor session level information.  The
scheduled_jobs_running.sql script uses this view to identify the
currently running jobs.  The extract function is used to retrieve
the elapsed time in seconds from the interval returned by the view.

 

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


*************************************************

set linesize 200

column owner format a15

column next_run_date format a20

 

select

   rj.job_name,

   rj.owner,

   to_char(j.next_run_date, ‘DD-MON-YYYY
HH24:MI:SS’) as next_run_date,

   extract(second from rj.elapsed_time) as
elapsed_time,

   rj.cpu_used,

   rj.session_id,

   rj.resource_consumer_group,

   j.run_count

from

   dba_scheduler_running_jobs rj,

   dba_scheduler_jobs j

where

   rj.job_name = j.job_name

order by

   rj.job_name

;

 

The output generated from the
scheduled_jobs_running.sql script is shown below.

 

SQL> @scheduled_jobs_running

 

JOB_NAME                       OWNER          
NEXT_RUN_DATE        ELAPSED_TIME   CPU_USED SESSION_ID
RESOURCE_CONSUMER_GROUP           RUN_COUNT

—————————— —————
——————– ———— ———-

TEST_FULL_JOB_DEFINITION       JOB_USER       
24-JUN-2004 09:22:00        20.69          0
       272                                         296

 

1 row selected.

 

This information is also available from OEM on
the Scheduler Jobs (Running) page (Administration > Jobs) shown in
Figure 5.2.

 

 

Figure 5.2 ? OEM 10g DB Control: Scheduler Jobs
(Running)

 

The Scheduler Jobs (Disabled) page of OEM,
shown in Figure 5.3, 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

 


*************************************************

— 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


*************************************************

 

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

 

1 row selected.

 

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

 

 

Figure 5.3 ? OEM 10g 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

 


*************************************************

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

 

5 rows selected.

 

 

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

 

5 rows selected.

 

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

 

 

Figure 5.4 ? OEM 10g 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

 


*************************************************

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

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

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

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 5.5,
which contains similar information to the scheduled_job_details.sql
script.

 

 

Figure 5.5 ? OEM 10g DB Control: View Job

 

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

 

 

Figure 5.6 ? OEM 10g 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.

 


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.


 

 

��

 

 

 

Oracle Training at Sea

 

 

 

 

oracle dba poster

 

 

Follow us on Twitter  

 

Oracle performance tuning software  

 

Oracle Linux poster

 

 
 

 

Burleson is the American Team

Note:

This Oracle

documentation was created as a support and Oracle training reference for use by our

DBA performance tuning consulting professionals. 

Feel free to ask questions on our

Oracle forum .

Verify

experience!

Anyone

considering using the services of an Oracle support expert should

independently investigate their credentials and experience, and not rely on

advertisements and self-proclaimed expertise. All legitimate Oracle experts

publish

their Oracle

qualifications .

Errata?

 Oracle technology is changing and we

strive to update our BC Oracle support information.  If you find an error

or have a suggestion for improving our content, we would appreciate your

feedback.  Just 

e-mail:

 

and include the URL for the page.


                    


Burleson Consulting


The Oracle of

Database Support

Oracle

Performance Tuning

Remote DBA Services


 

Copyright © 1996 –  2017

All rights reserved by

Burleson

Oracle ®

is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by
Conversational

 

Call now: 252-767-6166 

 

 Home
  E-mail Us

 Oracle

Articles

New Oracle Articles


 Oracle

Training
 Oracle

Tips


 Oracle

Forum
 Class

Catalog


 Remote

DBA
 Oracle

Tuning
 Emergency

911
 RAC

Support
 Apps

Support
 Analysis
 Design
 Implementation
 Oracle

Support


 SQL Tuning
 Security

 Oracle

UNIX
 Oracle

Linux
 Monitoring
 Remote s
upport
 Remote

plans
 Remote

services
 Application

Server

 Applications
 Oracle

Forms
 Oracle

Portal
 App

Upgrades
  SQL

Server
  Oracle Concepts
 Software Support
 Remote

S upport

 

 Development  

 Implementation


 Consulting Staff
 Consulting

Prices
 Help

Wanted!

 

 Oracle Posters
 Oracle Books

 Oracle

Scripts

 Ion

 Excel-DB  

Don Burleson Blog  

 

 

 


 

 

 

 

 

Monitoring Jobs in Oracle 10g

Oracle Tips by Burleson Consulting

Monitoring Jobs in 10g

Chapter 2 noted that Oracle10g includes a new
set of dba_scheduler_% views to display scheduler information.  This
section will examine the information that can be gathered regarding
scheduled jobs using these views and the OEM 10g DB Control.

 

The list of currently scheduled jobs is
displayed using the dba_scheduler_jobs view, which provides list of
job names and the basic schedule information.  The
scheduled_jobs.sql script shows how this view is used.

 

* scheduled_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

;

 

The output generated from the
scheduled_jobs.sql script is shown below.

 

SQL> @scheduled_jobs

 

JOB_NAME                       OWNER          
NEXT_RUN_DATE             LAST_RUN_DATE        JOB_CLASS                      
RUN_COUNT

—————————— —————
————————- ——————–
—————————— ———-

GATHER_STATS_JOB               SYS            
MAINTENANCE_WINDOW_GROUP  24-JUN-2004 08:09:39 AUTO_TASKS_JOB_CLASS                   
4

PURGE_LOG                      SYS            
24-JUN-2004 03:00:00      23-JUN-2004 03:00:01 DEFAULT_JOB_CLASS                     
19

TEST_FULL_JOB_DEFINITION       JOB_USER       
24-JUN-2004 08:52:00      24-JUN-2004 08:52:00 DEFAULT_JOB_CLASS                    
281

TEST_PROG_SCHED_CLASS_JOB_DEF  JOB_USER       
24-JUN-2004 09:00:16      24-JUN-2004 08:09:39 TEST_JOB_CLASS                        
16

ARGUMENT_JOB_DEFINITION        JOB_USER       
24-JUN-2004 09:00:16      24-JUN-2004 08:09:39 DEFAULT_JOB_CLASS                     
16

TEST_SCHED_JOB_DEFINITION      JOB_USER       
24-JUN-2004 09:00:16      24-JUN-2004 08:09:37 DEFAULT_JOB_CLASS                     
16

TEST_PROG_JOB_DEFINITION       JOB_USER       
24-JUN-2004 09:00:09      24-JUN-2004 08:09:38 DEFAULT_JOB_CLASS                     
16

TEST_PROG_SCHED_JOB_DEFINITION JOB_USER       
24-JUN-2004 09:00:16      24-JUN-2004 08:09:39 TEST_JOB_CLASS                        
16

 

8 rows selected.

 

This information is also available from OEM on
the Scheduler Jobs (Scheduled) page (Administration > Jobs) shown in
Figure 5.1.

 

 

Figure 5.1 ? OEM 10g DB Control: Scheduler Jobs
(Scheduled)

 

The dba_scheduler_running_jobs view is the real
starting point for job monitoring as it displays a list of the
currently running jobs.  Using this view, the user is able to
identify the session that is actually executing the job, giving the
ability to monitor session level information.  The
scheduled_jobs_running.sql script uses this view to identify the
currently running jobs.  The extract function is used to retrieve
the elapsed time in seconds from the interval returned by the view.

 

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


*************************************************

set linesize 200

column owner format a15

column next_run_date format a20

 

select

   rj.job_name,

   rj.owner,

   to_char(j.next_run_date, ‘DD-MON-YYYY
HH24:MI:SS’) as next_run_date,

   extract(second from rj.elapsed_time) as
elapsed_time,

   rj.cpu_used,

   rj.session_id,

   rj.resource_consumer_group,

   j.run_count

from

   dba_scheduler_running_jobs rj,

   dba_scheduler_jobs j

where

   rj.job_name = j.job_name

order by

   rj.job_name

;

 

The output generated from the
scheduled_jobs_running.sql script is shown below.

 

SQL> @scheduled_jobs_running

 

JOB_NAME                       OWNER          
NEXT_RUN_DATE        ELAPSED_TIME   CPU_USED SESSION_ID
RESOURCE_CONSUMER_GROUP           RUN_COUNT

—————————— —————
——————– ———— ———-

TEST_FULL_JOB_DEFINITION       JOB_USER       
24-JUN-2004 09:22:00        20.69          0
       272                                         296

 

1 row selected.

 

This information is also available from OEM on
the Scheduler Jobs (Running) page (Administration > Jobs) shown in
Figure 5.2.

 

 

Figure 5.2 ? OEM 10g DB Control: Scheduler Jobs
(Running)

 

The Scheduler Jobs (Disabled) page of OEM,
shown in Figure 5.3, 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

 


*************************************************

— 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


*************************************************

 

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

 

1 row selected.

 

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

 

 

Figure 5.3 ? OEM 10g 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

 


*************************************************

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

 

5 rows selected.

 

 

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

 

5 rows selected.

 

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

 

 

Figure 5.4 ? OEM 10g 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

 


*************************************************

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

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

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

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 5.5,
which contains similar information to the scheduled_job_details.sql
script.

 

 

Figure 5.5 ? OEM 10g DB Control: View Job

 

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

 

 

Figure 5.6 ? OEM 10g 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.

 


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.


 

 

��

 

 

 

Oracle Training at Sea

 

 

 

 

oracle dba poster

 

 

Follow us on Twitter  

 

Oracle performance tuning software  

 

Oracle Linux poster

 

 
 

 

Burleson is the American Team

Note:

This Oracle

documentation was created as a support and Oracle training reference for use by our

DBA performance tuning consulting professionals. 

Feel free to ask questions on our

Oracle forum .

Verify

experience!

Anyone

considering using the services of an Oracle support expert should

independently investigate their credentials and experience, and not rely on

advertisements and self-proclaimed expertise. All legitimate Oracle experts

publish

their Oracle

qualifications .

Errata?

 Oracle technology is changing and we

strive to update our BC Oracle support information.  If you find an error

or have a suggestion for improving our content, we would appreciate your

feedback.  Just 

e-mail:

 

and include the URL for the page.


                    


Burleson Consulting


The Oracle of

Database Support

Oracle

Performance Tuning

Remote DBA Services


 

Copyright © 1996 –  2017

All rights reserved by

Burleson

Oracle ®

is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by
Conversational

 

Skip navigation

  • Oracle.com
  • Downloads
  • Cloud Trials
  • Other Languages
    • Chinese
    • Japanese
    • Portuguese
    • Espanol
  • Go Directly To
    • Oracle Developer Community
    • My Oracle Support Community
    • OPN Cloud Connection
    • Oracle Employee Community
    • Oracle User Group Community
    • Topliners Community
    • Support Blogs
    • Java Community
  • Quick Links
    • Get Started Guide
    • FAQ
    • Ideas
    • Feedback
Oracle
Browse
    • Log in
    • Register

Error: You don’t have JavaScript enabled. This tool uses JavaScript and much of it will not work correctly without it enabled. Please turn JavaScript back on and reload this page.

Join the world’s largest interactive community dedicated to Oracle technologies.

  • Learn from thousands of community experts
  • Get answers to your technical questions
  • Share your knowledge with peers
1,060,000

Active Users

1,100,000

Answered Questions

Register Today
arrow right

This discussion is archived

    1
    2


    Previous
    Next

    20 Replies

    Latest reply on Apr 22, 2009 9:33 PM by rbglossip

    How to view Oracle job logs

    mohitanchlia


    mohitanchlia Dec 17, 2008 4:59 PM

      Oracle 10g:

      I have following job:

      procedure create_or_replace_job as
      begin
      begin
      dbms_scheduler.drop_job(job_name => merge_job_name);
      exception
      when others then

      commit;
      end;
      dbms_scheduler.create_job(job_name => merge_job_name,
      job_type => ‘STORED_PROCEDURE’,
      job_action => merge_proc_name,
      start_date => to_timestamp(’01-Jan-1970 ‘ || merge_job_time,
      ‘DD-Mon-RRRR HH24:MI:SS’),
      repeat_interval => ‘FREQ = DAILY; INTERVAL = 1’);
      commit;
      end;

      But I want to see when job ran and if there were any errors.

      I have the same question (0)

        This content has been marked as final. 

        Show 20 replies


        • 1.

          Re: How to view Oracle job logs

          Aman....

          Aman….

          Dec 17, 2008 5:07 PM


          ( in response to mohitanchlia )

          user628400 wrote:
          Oracle 10g:

          But I want to see when job ran and if there were any errors.

          You may want to check scheduler* views from doc for this. For example,
          [dba_scheduler_job_log|http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_2047.htm#i1587038]
          [dba_scheduler_job_run_details|http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_2048.htm#i1587156]
          HTH
          Aman….



          • 2.

            Re: How to view Oracle job logs

            591186

            591186

            Dec 17, 2008 5:09 PM


            ( in response to mohitanchlia )

            all_scheduler_job_log will give you some information.



            • 3.

              Re: How to view Oracle job logs

              LKBrwn_DBA

              LKBrwn_DBA

              Dec 17, 2008 5:10 PM


              ( in response to mohitanchlia )

              Would be easy if you use Enterprise Manager.
              Otherwise you need to look at:

              ALL _SCHEDULER_JOB_LOG view :p



              • 4.

                Re: How to view Oracle job logs

                247514

                247514

                Dec 17, 2008 5:25 PM


                ( in response to mohitanchlia )

                Did you check DBA view dba_scheduler_job_run_details ?

                lol , I answered a quick phone call before I hit submit button, can’t believe how many replies were posted during the interim.

                Edited by: yingkuan on Dec 17, 2008 9:23 AM



                • 5.

                  Re: How to view Oracle job logs

                  Aman....

                  Aman….

                  Dec 17, 2008 5:37 PM


                  ( in response to 247514 )

                  yingkuan wrote:
                  lol , I answered a quick phone call before I hit submit button, can’t believe how many replies were posted during the interim.

                  That’s why I was just sitting quietly and watching them flying all over :-).
                  Aman….



                  • 6.

                    Re: How to view Oracle job logs

                    mohitanchlia

                    mohitanchlia

                    Dec 17, 2008 5:54 PM


                    ( in response to Aman…. )

                    What could be the reason if I don’t see anything in the logs? But I do know that the job got executed.



                    • 7.

                      Re: How to view Oracle job logs

                      Aman....

                      Aman….

                      Dec 17, 2008 6:17 PM


                      ( in response to mohitanchlia )

                      user628400 wrote:
                      What could be the reason if I don’t see anything in the logs? But I do know that the job got executed.

                      It may be that the job’s log level is not properly set.
                      http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/scheduse002.htm#CHDGIDFD
                      HTH
                      Aman….



                      • 8.

                        Re: How to view Oracle job logs

                        mohitanchlia

                        mohitanchlia

                        Apr 22, 2009 7:56 PM


                        ( in response to Aman…. )

                        I am looking at how long it took for “GATHER_STATS_JOB” to run also when it started and when it ended. But none of the views that I queried give me that detail. I tried:

                        select * from dba_scheduler_job_run_details where job_name = 'GATHER_STATS_JOB' order by log_id desc
                        LOG_ID     LOG_DATE     OWNER     JOB_NAME     JOB_SUBNAME     STATUS     ERROR#     REQ_START_DATE     ACTUAL_START_DATE     RUN_DURATION     INSTANCE_ID     SESSION_ID     SLAVE_PID     CPU_USED     ADDITIONAL_INFO     
                        1741          SYS     GATHER_STATS_JOB     <NULL>     SUCCEEDED     0     <NULL>               1     2316,20537     25785          <NULL>     
                        select * from dba_scheduler_jobs
                        OWNER     JOB_NAME     JOB_SUBNAME     JOB_CREATOR     CLIENT_ID     GLOBAL_UID     PROGRAM_OWNER     PROGRAM_NAME     JOB_TYPE     JOB_ACTION     NUMBER_OF_ARGUMENTS     SCHEDULE_OWNER     SCHEDULE_NAME     SCHEDULE_TYPE     START_DATE     REPEAT_INTERVAL     EVENT_QUEUE_OWNER     EVENT_QUEUE_NAME     EVENT_QUEUE_AGENT     EVENT_CONDITION     EVENT_RULE     END_DATE     JOB_CLASS     ENABLED     AUTO_DROP     RESTARTABLE     STATE     JOB_PRIORITY     RUN_COUNT     MAX_RUNS     FAILURE_COUNT     MAX_FAILURES     RETRY_COUNT     LAST_START_DATE     LAST_RUN_DURATION     NEXT_RUN_DATE     SCHEDULE_LIMIT     MAX_RUN_DURATION     LOGGING_LEVEL     STOP_ON_WINDOW_CLOSE     INSTANCE_STICKINESS     RAISE_EVENTS     SYSTEM     JOB_WEIGHT     NLS_ENV     SOURCE     DESTINATION     COMMENTS     FLAGS     
                        SYS     GATHER_STATS_JOB     <NULL>     SYS     <NULL>     <NULL>     SYS     GATHER_STATS_PROG     <NULL>     <NULL>     <NULL>     SYS     MAINTENANCE_WINDOW_GROUP     WINDOW_GROUP     <NULL>     <NULL>     <NULL>     <NULL>     <NULL>     <NULL>     <NULL>     <NULL>     AUTO_TASKS_JOB_CLASS     TRUE     FALSE     TRUE     SCHEDULED     3     125     <NULL>     0     <NULL>     0               <NULL>     <NULL>     <NULL>     RUNS     TRUE     TRUE     <NULL>     TRUE     1     NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_CALENDAR='GREGORIAN' NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY' NLS_TIME_FORMAT='HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT='DD-MON-RR HH.MI.SSXFF AM' NLS_TIME_TZ_FORMAT='HH.MI.SSXFF AM TZR' NLS_TIMESTAMP_TZ_FORMAT='DD-MON-RR HH.MI.SSXFF AM TZR' NLS_DUAL_CURRENCY='$' NLS_COMP='BINARY' NLS_LENGTH_SEMANTICS='BYTE' NLS_NCHAR_CONV_EXCP='FALSE'     <NULL>     <NULL>     Oracle defined automatic optimizer statistics collection job     21074010     
                        select * from dba_schedular_schedules
                        OWNER     SCHEDULE_NAME     SCHEDULE_TYPE     START_DATE     REPEAT_INTERVAL     EVENT_QUEUE_OWNER     EVENT_QUEUE_NAME     EVENT_QUEUE_AGENT     EVENT_CONDITION     END_DATE     COMMENTS     
                        SYS     DAILY_PURGE_SCHEDULE     CALENDAR     <NULL>     freq=daily;byhour=3;byminute=0;bysecond=0     <NULL>     <NULL>     <NULL>     <NULL>     <NULL>     <NULL>     



                        • 9.

                          Re: How to view Oracle job logs

                          591186

                          591186

                          Apr 22, 2009 8:08 PM


                          ( in response to mohitanchlia )

                          Post the output of the below:

                          SELECT job_name, state FROM dba_scheduler_jobs WHERE job_name='GATHER_STATS_JOB';
                          select job_name, job_type, program_name, schedule_name, job_class from dba_scheduler_jobs where job_name = 'GATHER_STATS_JOB';
                          The actual_start_date and the run_duration will give you how long did it run.
                          SQL> desc dba_scheduler_job_run_details Name                                      Null?    Type ----------------------------------------- -------- ---------------------------- LOG_ID                                             NUMBER LOG_DATE                                           TIMESTAMP(6) WITH TIME ZONE OWNER                                              VARCHAR2(30) JOB_NAME                                           VARCHAR2(65) JOB_SUBNAME                                        VARCHAR2(65) STATUS                                             VARCHAR2(30) ERROR#                                             NUMBER REQ_START_DATE                                     TIMESTAMP(6) WITH TIME ZONE ACTUAL_START_DATE                                  TIMESTAMP(6) WITH TIME ZONE RUN_DURATION                                       INTERVAL DAY(3) TO SECOND(0) INSTANCE_ID                                        NUMBER SESSION_ID                                         VARCHAR2(30) SLAVE_PID                                          VARCHAR2(30) CPU_USED                                           INTERVAL DAY(3) TO SECOND(2) ADDITIONAL_INFO                                    VARCHAR2(4000)



                          • 10.

                            Re: How to view Oracle job logs

                            mohitanchlia

                            mohitanchlia

                            Apr 22, 2009 8:10 PM


                            ( in response to 591186 )

                            As you can see from my post above I tried that but it columns are empty. This is the jbos that Oracle runs every night.



                            • 11.

                              Re: How to view Oracle job logs

                              rbglossip

                              rbglossip

                              Apr 22, 2009 8:13 PM


                              ( in response to mohitanchlia )

                              It should be there.

                              SQL> select actual_start_date
                                2       , actual_start_date + run_duration end_date
                                3    from dba_scheduler_job_run_details
                                4   where log_id = (select max(log_id)
                                5                     from dba_scheduler_job_run_details
                                6                    where job_name = 'GATHER_STATS_JOB');
                              ACTUAL_START_DATE
                              ------------------------------------------------------------
                              END_DATE
                              ------------------------------------------------------------
                              21-APR-09 10.00.02.244907 PM -04:00
                              21-APR-09 10.00.36.244907000 PM -04:00

                              Your output is a bit hard to read. Can you try the query above in SQL*Plus?



                              • 12.

                                Re: How to view Oracle job logs

                                591186

                                591186

                                Apr 22, 2009 8:25 PM


                                ( in response to rbglossip )

                                oh. that was fast. I was just about to post the same… 😀

                                may be, my browser was thinking 🙂

                                Edited by: Anantha on Apr 22, 2009 4:25 PM



                                • 13.

                                  Re: How to view Oracle job logs

                                  rbglossip

                                  rbglossip

                                  Apr 22, 2009 8:16 PM


                                  ( in response to 591186 )

                                  Not too fast. Two replies posted while I was typing it 😉



                                  • 14.

                                    Re: How to view Oracle job logs

                                    591186

                                    591186

                                    Apr 22, 2009 8:19 PM


                                    ( in response to mohitanchlia )

                                    Post the output of:

                                    SELECT job_name, state FROM dba_scheduler_jobs WHERE job_name='GATHER_STATS_JOB';
                                    select job_name, job_type, program_name, schedule_name, job_class from dba_scheduler_jobs where job_name = 'GATHER_STATS_JOB';



                                    1
                                    2

                                    Previous
                                    Next

                                    Go to original post

                                    Actions