Edit D:\app\Administrator\product\11.2.0\dbhome_1\owb\rtp\sql\print_running_exec

Edit D:\app\Administrator\product\11.2.0\dbhome_1\owb\rtp\sql\print_running_exec_details.sql

REM
REM This script is used to print the Audit Execution Hierarchy and details
REM of all executions that are incomplete and were started since a provided date
REM 
REM The output is placed in a file called running_exec_details.txt
REM
REM Logon as WorkspaceOwner or WorkspaceUser with Execute/Deploy/Admin privilege
REM 
REM It takes two parameters representing Date and Workspace
REM
REM    @<OWB-HOME>/owb/rtp/sql/print_exec_details.sql <date> <workspace>
REM
REM All running jobs started after the given Date are selected
REM
REM The given workspace is the one in which the jobs are running
REM <workspace> should be declared as workspaceOwner.workspaceName
REM if only workspaceName is given, workspaceOwner will be defaulted to user
REM
REM    Date            Who           Comments
REM -----------------------------------------
REM  11th Nov 2003     GWatters      V1.0
REM  16th Nov 2003     RWhittington  V2.0
REM

define SINCE="&1."
define WORKSPACE=&2.

set role OWB_USER;
call owbsys.wb_rt_script_util.set_workspace('&WORKSPACE.');

set linesize 175
set pagesize 60
set newpage 0
set echo off
set verify off
set feedback off

repheader off
repfooter off

ttitle 'Audit Execution Report'

column top_level_execution_audit_id heading Top_Level_Id
column top_level_execution_audit_id format 99999999999
column top_level_execution_audit_id truncated

column audit_id heading Audit_Id
column audit_id format 99999999999
column audit_id truncated

column stream_id heading Stream_Id
column stream_id format 99999999999
column stream_id truncated

column parent_execution_audit_id heading Parent_Id
column parent_execution_audit_id format 99999999999
column parent_execution_audit_id truncated

column execution_audit_id heading Exec_Id
column execution_audit_id format 99999999999
column execution_audit_id truncated

column execution_audit_status heading Exec_Status
column execution_audit_status format a10
column execution_audit_status truncated

column event_type heading Event_Type
column event_type format a20
column event_type truncated

column ack_type heading Ack_Type

column ack_type format a20
column ack_type truncated

column run_status heading Map_Status
column run_status format a10
column run_status truncated

column step_count heading #Steps
column step_count format 9999
column step_count truncated

column created_on heading Created_On
column created_on format a25
column created_on truncated

column execution_name heading Name
column execution_name format a25
column execution_name truncated

column task_type heading TaskType
column task_type format a10
column task_type truncated

column exec_location_name heading Exec_Loc
column exec_location_name format a8
column exec_location_name truncated

column return_result heading Result
column return_result format a4
column return_result truncated

column return_code heading Code
column return_code format 999
column return_code truncated

column number_task_errors heading #Errors
column number_task_errors format 9999
column number_task_errors truncated

column number_task_warnings heading #Warns
column number_task_warnings format 9999
column number_task_warnings truncated

column corr_id heading Stream_id
column corr_id format a12
column corr_id truncated

column msg_state heading Msg_State
column msg_state format a10
column msg_state truncated

column enq_time heading Eng_Time
column enq_time format a25
column enq_time truncated

break on top_level_execution_audit_id skip 2

spool running_exec_details.txt

select ex.top_level_execution_audit_id, 
       ex.parent_execution_audit_id, 
       ex.execution_audit_id,
       to_char(ex.created_on, 'YYYY/MM/DD-HH24:MI:SS') created_on, 
       ex.execution_name,
       ex.task_type,
       ex.exec_location_name,
       ex.execution_audit_status,
       mr.run_status,
       count(sr.step_id) step_count,
       ex.return_result, 
       ex.return_code,
       ex.number_task_errors, 
       ex.number_task_warnings
from 
      owbsys.all_rt_audit_executions ex,
      owbsys.all_rt_audit_map_runs mr,
      owbsys.all_rt_audit_step_runs sr
where 
      (ex.execution_audit_id in (select execution_audit_id from owbsys.all_rt_audit_executions where execution_audit_status <> 'COMPLETE' and created_on >= &SINCE) or 
       ex.parent_execution_audit_id in (select execution_audit_id from owbsys.all_rt_audit_executions where execution_audit_status <> 'COMPLETE' and created_on >= &SINCE) or
       ex.execution_audit_id in
          (select 
             top_level_execution_audit_id
           from
             owbsys.all_rt_audit_executions
           where
             execution_audit_id in (select execution_audit_id from owbsys.all_rt_audit_executions where execution_audit_status <> 'COMPLETE' and created_on >= &SINCE)) or
       ex.execution_audit_id in
          (select 
             parent_execution_audit_id
           from
             owbsys.all_rt_audit_executions
           where
             execution_audit_id in (select execution_audit_id from owbsys.all_rt_audit_executions where execution_audit_status <> 'COMPLETE' and created_on >= &SINCE)) or
       ex.parent_execution_audit_id in
          (select 
             parent_execution_audit_id
           from
             owbsys.all_rt_audit_executions
           where
             execution_audit_id in (select execution_audit_id from owbsys.all_rt_audit_executions where execution_audit_status <> 'COMPLETE' and created_on >= &SINCE))
       ) and
       ex.execution_audit_id=mr.execution_audit_id(+) and
       mr.map_run_id=sr.map_run_id(+) 
group by
       ex.top_level_execution_audit_id, 
       ex.parent_execution_audit_id, 
       ex.execution_audit_id,
       to_char(ex.created_on, 'YYYY/MM/DD-HH24:MI:SS'), 
       ex.execution_name,
       ex.task_type,
       ex.exec_location_name,
       ex.execution_audit_status,
       mr.run_status,
       ex.return_result, 
       ex.return_code,
       ex.number_task_errors, 
       ex.number_task_warnings
order by
      ex.top_level_execution_audit_id,
      ex.execution_audit_id;
      
break on audit_id skip 2

ttitle 'Audit Execution Acks'

select 
    ac.audit_id,
    ac.stream_id,
    decode(ac.ack_type, 
           20001, 'NONE', 
           20002, 'RECOVERIES',  
           20003, 'ERRORS', 
           20004, 'WARNINGS', 
           ack_type) ack_type, 
    ac.ack_pending
from
    owbsys.wb_rtv_audit_notifications ac,
    owbsys.all_rt_audit_executions ex
where 
      (ex.execution_audit_id in (select execution_audit_id from owbsys.all_rt_audit_executions where execution_audit_status <> 'COMPLETE' and created_on >= &SINCE) or 
       ex.parent_execution_audit_id in (select execution_audit_id from owbsys.all_rt_audit_executions where execution_audit_status <> 'COMPLETE' and created_on >= &SINCE) or
       ex.execution_audit_id in
          (select 
             top_level_execution_audit_id
           from
             owbsys.all_rt_audit_executions
           where
             execution_audit_id in (select execution_audit_id from owbsys.all_rt_audit_executions where execution_audit_status <> 'COMPLETE' and created_on >= &SINCE)) or
       ex.execution_audit_id in
          (select 
             parent_execution_audit_id
           from
             owbsys.all_rt_audit_executions
           where
             execution_audit_id in (select execution_audit_id from owbsys.all_rt_audit_executions where execution_audit_status <> 'COMPLETE' and created_on >= &SINCE)) or
       ex.parent_execution_audit_id in
          (select 
             parent_execution_audit_id
           from
             owbsys.all_rt_audit_executions
           where
             execution_audit_id in (select execution_audit_id from owbsys.all_rt_audit_executions where execution_audit_status <> 'COMPLETE' and created_on >= &SINCE))
       ) and
       ac.notification_type = 'A' and
       ex.execution_audit_id=ac.audit_id
order by
      ac.audit_id,
      ac.stream_id;

ttitle 'Audit Execution Interests'

select 
    nc.audit_id,
    nc.stream_id,
    decode(nc.event_type, 
           1001, 'UNIT_OP_START',
           1002, 'UNIT_OP_COMPLETE',
           1003, 'EXEC_OP_START',
           1004, 'EXEC_OP_COMPLETE',
           1005, 'SCRIPT_RUN_BEGIN',
           1006, 'SCRIPT_RUN_END',
           1007, 'TASK_BEGIN',
           1008, 'TASK_END',
           1009, 'FILE',
           1010, 'INFORMATIONAL',
           1011, 'WARNING',
           1012, 'ERROR',
           1013, 'RECOVERY',
           1014, 'PREPARED',
           1015, 'UNPREPARED',
           1016, 'TIMEOUT',
           event_type) event_type
from
    owbsys.wb_rtv_audit_notifications nc,
    owbsys.all_rt_audit_executions ex
where 
      (ex.execution_audit_id in (select execution_audit_id from owbsys.all_rt_audit_executions where execution_audit_status <> 'COMPLETE' and created_on >= &SINCE) or 
       ex.parent_execution_audit_id in (select execution_audit_id from owbsys.all_rt_audit_executions where execution_audit_status <> 'COMPLETE' and created_on >= &SINCE) or
       ex.execution_audit_id in
          (select 
             top_level_execution_audit_id
           from
             owbsys.all_rt_audit_executions
           where
             execution_audit_id in (select execution_audit_id from owbsys.all_rt_audit_executions where execution_audit_status <> 'COMPLETE' and created_on >= &SINCE)) or
       ex.execution_audit_id in
          (select 
             parent_execution_audit_id
           from
             owbsys.all_rt_audit_executions
           where
             execution_audit_id in (select execution_audit_id from owbsys.all_rt_audit_executions where execution_audit_status <> 'COMPLETE' and created_on >= &SINCE)) or
       ex.parent_execution_audit_id in
          (select 
             parent_execution_audit_id
           from
             owbsys.all_rt_audit_executions
           where
             execution_audit_id in (select execution_audit_id from owbsys.all_rt_audit_executions where execution_audit_status <> 'COMPLETE' and created_on >= &SINCE))
       ) and
       nc.notification_type = 'C' and
       ex.execution_audit_id=nc.audit_id
order by
      nc.audit_id,
      nc.stream_id;

ttitle 'Nofity Queue Messages'

select 
    corr_id,
    msg_state, 
    to_char(enq_time, 'YYYY/MM/DD-HH24:MI:SS') enq_time
from
    owbsys.wb_rtv_queue_tabs_info
where
    queue_type = 'N' and
    corr_id in (select distinct
                      ns.stream_id
                from 
                      owbsys.wb_rtv_audit_notifications nc,
                      owbsys.wb_rtv_audit_notifications ac,
                      owbsys.wb_rtv_audit_notifications ns,
                      owbsys.all_rt_audit_executions ex
                where
                      (ex.parent_execution_audit_id in (select execution_audit_id from owbsys.all_rt_audit_executions where execution_audit_status <> 'COMPLETE' and created_on >= &SINCE) or 
                       ex.execution_audit_id in (select execution_audit_id from owbsys.all_rt_audit_executions where execution_audit_status <> 'COMPLETE' and created_on >= &SINCE) or
                       ex.execution_audit_id in
                          (select 
                             top_level_execution_audit_id
                           from
                             owbsys.all_rt_audit_executions
                           where
                             execution_audit_id in (select execution_audit_id from owbsys.all_rt_audit_executions where execution_audit_status <> 'COMPLETE' and created_on >= &SINCE)) or
                       ex.execution_audit_id in
                          (select 
                             parent_execution_audit_id
                           from
                             owbsys.all_rt_audit_executions
                           where
                             execution_audit_id in (select execution_audit_id from owbsys.all_rt_audit_executions where execution_audit_status <> 'COMPLETE' and created_on >= &SINCE)) or
                       ex.parent_execution_audit_id in
                          (select 
                             parent_execution_audit_id
                           from
                             owbsys.all_rt_audit_executions
                           where
                             execution_audit_id in (select execution_audit_id from owbsys.all_rt_audit_executions where execution_audit_status <> 'COMPLETE' and created_on >= &SINCE))
                      ) and
                      nc.notification_type = 'C' and
                      ac.notification_type = 'A' and
                      ns.notification_type = 'S' and
                      ((ns.stream_id=nc.stream_id and ex.execution_audit_id=nc.audit_id) or
                       (ns.stream_id=ac.stream_id and ex.execution_audit_id=ac.audit_id))
                )
order by
      corr_id,
      enq_time;

ttitle 'All Service Queue Messages'

select 
    corr_id,
    msg_state, 
    to_char(enq_time, 'YYYY/MM/DD-HH24:MI:SS') enq_time
from
    owbsys.wb_rtv_queue_tabs_info
where
    queue_type = 'S'
order by
      corr_id,
      enq_time;

spool off

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de