Sunday, August 16, 2015

Oracle : Script to check whats executing in the database right now

select /*+ RULE CUSROR_SHARING_EXACT */
  substrb(vs.username,1,10) db_user,
  substrb(to_char(vs.sid),1,4) sid,
  decode(bitand(vt.flag,4194304),0,'LOCAL','REMOTE') "LOCAL",
  decode(bitand(vt.flag,268435456),0,'RDC','SRL') "ISO",
  substrb(to_char(vt.log_io+vt.phy_io,'999,999,999'),2,7) totl_io,
  substrb(to_char(vt.log_io/greatest((vt.log_io+vt.phy_io),1)*100,'999.99')  ||'%',2,7) hit_rt,
  to_char(nvl(vt.used_ublk,0),'999,999') undo,
  lpad(substrb(decode(trunc(sysdate-to_date(vt.start_time,'MM/DD/YY HH24:MI:SS')),0,
    to_char(to_date(trunc(86400*abs(sysdate-to_date(vt.start_time,'MM/DD/YY HH24:MI:SS'))),'SSSSS'),'HH24:MI:SS'),
    to_char(trunc(sysdate-to_date(vt.start_time,'MM/DD/YY HH24:MI:SS')),'B999999')),1,10),8) "ST-D/HMS",
  vs.sql_address sql_addr
from v$session vs, v$transaction vt
where vs.type != 'BACKGROUND'
and vs.taddr = vt.addr
order by 5 desc, 3 desc
/

No comments:

Post a Comment