DESCRIPTION
This custom report will list the Top N SQL statements for the specified session id (SID). This is useful when looking back to see what the last SQL a session ran before having issue, like a locking situation or similar. To use this report, run it from SQL*Plus while connected to the Ignite repository database.
SQL*PLUS SCRIPT
select id, name from cond order by name;
prompt Which Database ID
accept DBID
prompt Enter Start Date (mm/dd/yy hh24:mi)
accept BeginDate
prompt Enter End Date (mm/dd/yy hh24:mi)
accept EndDate
prompt Enter the Session Id (i.e sid.serial# if Oracle, SPID if SQL Server)
accept SPID
Rem
set pagesize 999
set linesize 132
column object_name format a80
SELECT MIN(d) START_DATE,
vdsi SPID,
NVL(sql_text(&DBID,izho),'DDL or Commits') SQL_TEXT, SUM(qp/100) timesecs
FROM consw_&DBID sw, conev_&DBID ev
WHERE sw.d BETWEEN TO_DATE('&BeginDate','mm/dd/yy hh24:mi') AND TO_DATE('&EndDate','mm/dd/yy hh24:mi')
AND sw.keeq = ev.id
AND sw.vdsi like '&SPID%'
GROUP BY vdsi, izho
ORDER BY min(d)
/
