OWB runtime repository

12 05 2011

I have been looking around the OWB runtime repository from time to time. Mainly because the Control Center isn’t allways the speedy friend you need when things get tough. It shows a lot of white screen a lot of the time while waiting for results to show. So I made myself a view on the runtime repository. I have been meaning to share it for some time, but did not get around to it. Until I recently saw a bit of much needed and long overdue OWB 11gR2 documentation for the runtime repository. I have not checked if I have not taken any shortcuts through the model yet, but when that leads to improvement, I will publish them here. So here it is.


CREATE OR REPLACE VIEW VW_RT_AUDIT_INFO
(EXECUTION_NAME, RETURN_RESULT, STARTTIME, ENDTIME, ELAPSE_TIME,
ELAPSE_FORMAT, SELECTED, INSERTED, UPDATED, DELETED,
DISCARDED, MERGED, CORRECTED, ERROR#, EXECUTION_AUDIT_STATUS,
MESSAGE_SEVERITY, MESSAGE_TEXT, PARAMETER_NAME, VALUE, CREATION_DATE,
OBJECT_NAME, OBJECT_LOCATION_NAME, TASK_NAME, TOP_LEVEL_EXECUTION_AUDIT_ID, EXECUTION_AUDIT_ID,
PARENT_EXECUTION_AUDIT_ID)
AS
SELECT    e.execution_name,
e.return_result,
e.created_on starttime,
e.updated_on endtime,
e.elapse_time,
TO_CHAR (TRUNC (SYSDATE, 'DD') + e.elapse_time / (24 * 3600),
'HH24:MI:SS') AS elapse_format,
DECODE (x.sel, NULL, 0, x.sel) AS selected,
DECODE (x.ins, NULL, 0, x.ins) AS inserted,
DECODE (x.upd, NULL, 0, x.upd) AS updated,
DECODE (x.del, NULL, 0, x.del) AS deleted,
DECODE (x.dis, NULL, 0, x.dis) AS discarded,
DECODE (x.mer, NULL, 0, x.mer) AS merged,
DECODE (x.cor, NULL, 0, x.cor) AS corrected,
DECODE (x.err, NULL, 0, x.err) AS error#,
e.execution_audit_status,
m.message_severity,
m.message_text,
p.parameter_name,
p.VALUE,
m.created_on AS creation_date,
e.object_name,
e.object_location_name,
e.task_name,
e.top_level_execution_audit_id,
e.execution_audit_id,
e.parent_execution_audit_id
FROM      all_rt_audit_executions e
LEFT JOIN all_rt_audit_exec_messages m
ON        e.execution_audit_id = m.execution_audit_id
LEFT JOIN all_rt_audit_execution_params p
ON        e.execution_audit_id = p.execution_audit_id
-- AND       p.parameter_name LIKE '%SPEELR%'
AND       p.parameter_name NOT IN ('PROCEDURE_NAME', 'PURGE_GROUP', 'OPERATING_MODE', 'MAX_NO_OF_ERRORS', 'AUDIT_LEVEL', 'BULK_SIZE', 'COMMIT_FREQUENCY', 'ODB_STORE_UOID', 'PACKAGE_NAME')
LEFT JOIN
(SELECT   e.execution_audit_id,
SUM (a.number_errors) AS err,
SUM (a.number_records_selected) AS sel,
SUM (a.number_records_inserted) AS ins,
SUM (a.number_records_updated) AS upd,
SUM (a.number_records_deleted) AS del,
SUM (a.number_records_discarded) AS dis,
SUM (a.number_records_merged) AS mer,
SUM (a.number_records_corrected) AS cor
FROM      all_rt_audit_executions e
LEFT JOIN all_rt_audit_map_runs a
ON e.execution_audit_id = a.execution_audit_id
GROUP BY e.execution_audit_id) x
ON         e.execution_audit_id = x.execution_audit_id




Note:

I have included error messages for each execution. This means rows will be duplicated when more than one error message is found for an execution.

Note 2:

I excluded the ‘default’ parameters for each execution because they too would lead to duplication of rows and most parameters will have default values anyway. Custom parameter values during execution will be shown in this way.

About these ads

Actions

Information

One response

13 03 2013

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

%d bloggers like this: