/*
-- Creating an external table that points to the alert log is a
-- common trick amongst DBAs, allowing you to read the log
-- without having to open an OS session and giving you the query
-- power of SQL against the log contents.
--
-- This tip is an example of just how powerful this trick can be.
-- Here the alert log is read and parsed for errors created by
-- database jobs (either the new DBMS_SCHEDULER jobs or old style DBMS_JOB jobs).
--
-- This is useful because the behaviour of a job queue process is to dump
-- any unhandled exception that interrupts the process into the alert log (as well as a trace
-- file for the job queue process), not into any database table or v$ view as one would expect.
-- (however new style DBMS_SCHEDULER jobs do also put the error number on the
-- DBA_SCHEDULER_JOB_RUN_DETAILS view).
--
-- As we know the format of the error text placed in the alert log
-- by the job queue processes, we can use analytic functions to
-- extract the error entry and present it as a one row per error
-- in a view.
--
-- The format of an error stack entry in the log created by a job queue process is:
--
-- Dy Mon DD hh24:mi:ss yyyy (a datetime entry common to all alert log entries)
-- Errors in file <name of trace file generated note the trailing colon>:
-- ORA-12012: error on auto execute of job X (this indicates that this entry was from a job)
-- ORA-?????: the first line of the error stack
-- <then any number of lines depending the size of the error stack>
-- Dy Mon DD hh24:mi:ss yyyy (the datetime of the next entry in the alert log)
--
-- From this we can parse out:
-- The time of the error
-- The name of the trace file generated
-- The job number (so we can link to dba_jobs)
-- The exception text raised by the job
-- And possibly other parts of the error stack
--
-- By creating a view from this query, we can now just query it
-- to see if any jobs encountered errors.
-- This code has been tested and works as intended on:
-- 9.2.0.5, 10.1.0.4 and 10.2.0.3.
--
*/

create or replace view dba_job_errors as
select
/* 40 chars is the length of:
'ORA-12012: error on auto execute of job '
leaving us with a job number! */
to_number(trim(substr(job_error_line,40))) as job,
/* turn the datetime of the entry into a date using the observed
format mask used by the alert log */
to_date(error_date, 'Dy Mon DD hh24:mi:ss yyyy') as error_date,
/* extract the file name:
15 is the length of 'Errors in file ', so the file name starts at 16
take off the trailing colon,
l is returned by the query from v$parameter below, add 1
to get the start of the file name
finally substr to 64 chars so our column isn't too wide
*/
substr(substr(rtrim(substr(trace_file_line,16),':'),l+1),1,64) as trace_file,
/* no parsing here, just substr to make the column a suitable width */
substr(the_error,1,128) as error_line1,
/* depending on which line was found to be the last one, either show the line
as a column or null it out
*/
substr(
( case
when (last_line <= 2) then '' else error_2
end
),1,128
) as error_line2,
substr(
( case
when (last_line <= 3) then '' else error_3
end
),1,128
) as error_line3,
substr(
( case
when (last_line <= 4) then '' else error_4
end
),1,128
) as error_line4
from
( /* subscaler query to get the lines from the log using
lag and lead analytics to transform rows into columns */
select
/* don't actually use line_number but might be useful */
line_number,
/* the line two before is the datetime of the entry and therefore
when the error occurred */
lag(text,2) over (order by line_number) as error_date,
/* the line immediately before is the trace file name */
lag(text,1) over (order by line_number) as trace_file_line,
/* this is the line containing the ORA-12012 */
text as job_error_line,
/* the line immediately after is the exception encountered */
lead(text,1) over (order by line_number) as the_error,
/* following lines may or may not be part of the error stack
there is no reason why this could not be extended to cope with huge error
stacks, by just adding more columns with lead(text, X) */
lead(text,2) over (order by line_number) as error_2,
lead(text,3) over (order by line_number) as error_3,
lead(text,4) over (order by line_number) as error_4,
/* if any of the leading text lines is like a datetime entry
then we know which line is the last line of our stack
10g could use REGEXP_LIKE here */
case
when (lead(text,2) over (order by line_number) like '___ ___ __ __:__:__ ____')
then 2
when (lead(text,3) over (order by line_number) like '___ ___ __ __:__:__ ____')
then 3
when (lead(text,4) over (order by line_number) like '___ ___ __ __:__:__ ____')
then 4
/* didn't find the next datetime in our lines so all lines are part of this errorstack */
else 5
end as last_line
from
/* the external table that points to our alert log */
alert_log
),
( /*
Sub scaler query to get the location of the trace files
generated so we can parse out the file name.
Beware, Windows actually puts job trace files
in the user_dump_dest location but we are only interested
in the length of the value, they should be the same,
assuming a convention of ..admin[/|\]sid[/|\][u|b|c|a]dump
Here we note that the path as shown in v$parameter
contains a trailing file seperator. If your entry
doesn't have this, you will have add one to this value,
eg: select length(value)+1 as l...
*/
select length(value) as l from v$parameter where name = 'background_dump_dest'
)
where
/* only interested in job errors
this is cartesian join as there is no link between the two queries
but thats OK as the v$parameter subscaler query only ever returns 1 row
*/
job_error_line like 'ORA-12012: error on auto execute of job%'
/