v$session join v$process

–10g
select s.sid, s.serial#, p.program, p.spid, s.username, w.event, s.last_call_et, w.seconds_in_wait, q.sql_text
from v$sql q, v$session s, v$session_wait w, v$process p
where q.sql_id (+) = s.sql_id
and q.address (+) = s.sql_address
and q.child_number (+) = s.sql_child_number
and s.sid = w.sid
and s.paddr = p.addr
and s.status = ‘ACTIVE’
and s.type != ‘BACKGROUND’
order by s.last_call_et desc

–9i
select nvl(s.username,’(oracle)’) as username,
s.osuser,s.sid,s.serial#,p.spid
,p.spid,s.lockwait,s.status,
s.machine,s.program
,to_char(s.logon_time,’DD-MON-YYYY HH24:MI:ss’) as logon_time
from v$session s, v$process p
where s.paddr =p.addr
– and p.spid= 8317
–and s.sid=31
– and s.status=’ACTIVE’
order by s.username,s.osuser

Thursday, November 13th, 2008 at 08:39
  • Cindy
    Feb 16th, 2012 at 22:33 | #1

    Thanks! I like your script for 10g!

Leave a comment

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>