Come per le query dei cursori visti qualche giorno fa io le elenco di seguito, poi le approfondite voi io non ve le spiego.
- Info Locations
SELECT location.name NAME
,location.description
,location.loctypeversion version
,location.loctype
,location.elementid
,prop.logicalname
,prop.value VALUE
,prop.createdby
,row_number () over (partition by location.name order by location.name) num_rec
FROM cmplogicallocation_v location
,cmpstringpropertyvalue_v prop
WHERE location.elementid = prop.propertyowner;
- Analisi Esecuzioni
select
o.object_name "Object Name",
o.object_type "Type",
eo.created_on "Created On"
from all_rt_objects o,
(select
e.object_uoid as euoid,
max(created_on) created_on
from
all_rt_audit_executions e
group by e.object_uoid) eo
where
eo.euoid=o.object_uoid order by object_name;
- Process Flows
select distinct object_name PROCESS_NAME from all_rt_tasks where task_type='ProcessFlow' order by object_name;
- Process Flows Attivi
select p.execution_audit_id "Audit ID",c.task_name "Activity Name",
c.execution_audit_id "Activity ID", c.parent_execution_audit_id "Parent ID",
c.exec_location_name "Location",
c.return_result "Result", ml.message_text "Audit Text"
from all_rt_audit_executions c, all_rt_audit_executions p,
all_rt_audit_exec_messages ml
where c.parent_execution_audit_id=p.execution_audit_id and
p.object_name=:PROCESS_NAME and
p.object_type='ProcessFlow' and
ml.execution_audit_id(+)=c.execution_audit_id
order by p.created_on DESC, c.execution_audit_id DESC;
- Process Flows Overview
select to_char(p.created_on, 'HH:MM:SS DD-MON-YY') "Exec Date",p.execution_audit_id "Audit ID", p.return_Result "Result"
from all_rt_audit_executions p
where p.object_name=:PROCESS_NAME and
object_type='ProcessFlow'
order by p.created_on DESC, p.execution_audit_id DESC;
- Parametri di esecuzione
select execution_audit_id "Audit Id", parameter_name "Parameter", parameter_type "Type", value "Value" from all_rt_audit_execution_params
where execution_audit_id in (
select distinct execution_audit_id from all_rt_audit_executions
where object_name=:PROCESS_NAME and
object_type='ProcessFlow')
order by execution_audit_id DESC, parameter_audit_id ASC;
- Mapping
select distinct object_name MAPPING_NAME from all_rt_tasks where (object_type='SQLLoaderControlFile' or object_type='ABAPFile' or object_type = 'PLSQLMap' or object_type = 'Mapping') order by object_name;
- Throughput
select r.start_time startd, mr.map_name "Map Name"
, t.target_name "Target"
,r.step_id "Step"
, to_char(r.start_time,'DD-MON hh24:mi') "Exec Date"
, to_char(r.end_time,'DD-MON hh24:mi') "End"
, to_char(trunc(r.elapse_time/60))
|| 'm '
|| to_char(r.elapse_time - 60 * trunc(r.elapse_time/60))
|| 's' "Exec Time"
, substr(r.step_type,1,3) "Type"
, nvl(r.number_records_merged,0)+nvl(r.number_records_inserted,0)+nvl(r.number_records_updated,0) "No Records"
, decode( nvl(r.elapse_time,0)
, 0, 'N/A'
, to_char(trunc((nvl(r.number_records_merged,0)+nvl(r.number_records_inserted,0)+nvl(r.number_records_updated,0)) / r.elapse_time , 2))
) ||' Per Second' "Throughput"
from all_rt_audit_map_runs mr
, all_rt_audit_step_runs r
, all_rt_audit_step_run_targets t
where (mr.map_name=:MAPPING_NAME or substr(mr.map_name, 2, length(mr.map_name)-2) = :MAPPING_NAME)
and mr.map_run_id = r.map_run_id
and mr.map_run_id = t.map_run_id
and r.step_id = t.step_id
and r.run_status = 'COMPLETE'
order by startd DESC;
- OwbErr
SELECT ALL_IV_RUN_ERROR.ERROR_MESSAGE
, ALL_IV_MAP_RUN.MAP_NAME
, ALL_IV_RUN_ERROR.RUN_TARGET_NAME
, to_char(ALL_IV_MAP_RUN.START_TIME,'dd-mon-yyyy hh24:mi:ss')
, ALL_IV_MAP_RUN.NUMBER_ERRORS
, ALL_IV_MAP_RUN.NUMBER_RECORDS_DELETED
, ALL_IV_MAP_RUN.NUMBER_RECORDS_DISCARDED
, ALL_IV_MAP_RUN.NUMBER_RECORDS_INSERTED
, ALL_IV_MAP_RUN.NUMBER_RECORDS_SELECTED
, ALL_IV_MAP_RUN.NUMBER_RECORDS_UPDATED
FROM DC_PRIMO_LIV.ALL_IV_MAP_RUN ALL_IV_MAP_RUN
, DC_PRIMO_LIV.ALL_IV_RUN_ERROR ALL_IV_RUN_ERROR
WHERE ( ( ALL_IV_MAP_RUN.MAP_RUN_ID = ALL_IV_RUN_ERROR.MAP_RUN_ID ) )
AND ( UPPER(ALL_IV_MAP_RUN.MAP_NAME) LIKE UPPER('%DPPS%') )
-- AND (UPPER( ALL_IV_RUN_ERROR.ERROR_MESSAGE) LIKE '%ANALYZE%')
AND ( ALL_IV_MAP_RUN.START_TIME >= '27/04/2016' )
ORDER BY ALL_IV_MAP_RUN.START_TIME;
E potremmo continuare cosi per un bel pò.
Se a qualcuno possono servire basta che mi contatti e mando l'xml da importare come Report di SqlDeveloper.
Di seguito un elenco delle query disponibili.
Ciao
Nessun commento:
Posta un commento