DBA FUS‎ > ‎

Shows current statements for active sessions

posted Sep 11, 2010, 5:26 AM by Sachchida Ojha
select   p.username   pu , s.username su, s.status  stat, s.sid  ssid,   s.serial# sser ,  substr(p.spid,1,8)   spid, substr(sa.sql_text,1,2000) txt
from     v$process p ,  v$session s,  v$sqlarea sa where    p.addr = s.paddr and  s.username  is not null and s.sql_address = sa.address(+)  and  s.sql_hash_value = sa.hash_value(+)
and      s.status   = 'ACTIVE' order by 1,2,7 ;

select s.sid, s.serial#, p.spid
from v$session s, v$process p
where s.paddr = p.addr
and s.sid in (select SESSION_ID from v$locked_object);