Sunday, August 16, 2015

Oracle : Script shows the last call for each session

select /*+ RULE CURSOR_SHARING_EXACT */
  substrb(to_char(vss.sid),1,4) sid,
  decode(vss.lockwait,null,substrb(vss.status,1,3),'WAI') "STA",
  lpad(substrb(decode(trunc(vss.last_call_et/86400),0,
  to_char(to_date(vss.last_call_et,'SSSSS'),'HH24:MI:SS'),
  to_char(trunc(vss.last_call_et/86400),'B999999')),1,10),8) "LC-D/HMS",
  vsa.address sql_addr,
  substrb(vsa.sql_text,1,45) sql_text
from v$session vss, v$sqlarea vsa
where vss.type != 'BACKGROUND'
and vss.sql_address = vsa.address(+)
--and to_char(vss.sid) like '%$1'
union all
select
  substrb(to_char(vss.sid),1,4) sid,
  decode(vss.lockwait,null,substrb(vss.status,1,3),'WAI') "STA",
  lpad(substrb(decode(trunc(vss.last_call_et/86400),0,
  to_char(to_date(vss.last_call_et,'SSSSS'),'HH24:MI:SS'),
  to_char(trunc(vss.last_call_et/86400),'B999999')),1,10),8) "LC-D/HMS",
  vsa.address sql_addr,
  substrb(vsa.sql_text,1,45) sql_text
from v$session vss, v$sqlarea vsa
where vss.prev_sql_addr != '00'
and vss.type != 'BACKGROUND'
and vss.prev_sql_addr = vsa.address(+)
--and to_char(vss.sid) like '%$1'
order by 2, 1
/

No comments:

Post a Comment