Here you have an example of a script that does just that using analytic functions.
It shows every map within the context of the driving procesflow (one level deep)
Even errors are shown if any occurred.
(run on 11gr1 but should work on 11gr2 as well)
The example shows only relevant runs of las week (sysdate - 7)
psec means previous seconds (yesterday)
asec means average seconds of last week
I use it in all my projects to get and keep stable load processes.
lag (amr.elapse_time, 1) over (partition by amr.map_name, ae.object_location_name order by amr.start_time) psec,
round (amr.elapse_time / 60, 1) min,
avg (amr.elapse_time) over (partition by amr.map_name, ae.object_location_name) asec,
ae.top_level_execution_audit_id = tae.execution_audit_id(+)
and ae.execution_audit_id = amr.execution_audit_id(+)
and ae.execution_audit_id = aum.execution_audit_id(+)
and amr.start_time >= sysdate - 7 -- last 7 days only
Very useful. Thank you. I was looking around for other ideas and found your query. I want to run a query that will show me all OWB jobs coming from different projects. Right now I have five different OWB projects and workflow jobs on one production instance. I should be able to run this in OWBSYS to see all of them? I will need to check. Just started looking into this.
There is also a set of reports available in a zip here: Oracle PL/SQL Sample Code. Scroll down to "OWB SQL Developer Reports". These then can be installed in Oracle SQL Developer.
Also, is there somewhere a document that elaborates more on public views, like for instance all_rt_audit_exec_messages and all the other deployment and runtime public views?
Of course there is the doco, but it doesn't elaborate about each one of them: Public Views for the Runtime Environmenthttp://www.findthatpdf.com/search-59603838-hPDF/download-documents-owb11gr2runtimemodel.pdf.htm
And then there is this great find with the ER model for runtime. Have not seen it in the past.