Show current statements for active sessions

posted Sep 8, 2010, 5:18 PM 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 ;