Active session sqls


SELECT area.HASH_VALUE,
 s.server,
 s.SID,
 p.spid,
 p.username "Unix User",
 s.username "Oracle User",
 DECODE(s.terminal, NULL, p.terminal, s.terminal) terminal,
 s.machine,
 w.event waitevent,
 w.SECONDS_IN_WAIT wait_in_sec,
 sql_text SQL,
 s.row_wait_file#,
 s.row_wait_block#,
 s.row_wait_row#,
 SUBSTR(TO_CHAR(logon_time, 'yymmdd hh24:mi'), 1, 14) logtime,
 buffer_gets gets,
 disk_reads READS,
 executions execs,
 sorts sorts,
 p.latchwait,
 s.lockwait
 FROM v$process p, v$session s, v$sqlarea area, v$session_wait w
 WHERE s.paddr = p.addr
 AND s.sql_address = area.address
 AND s.sql_hash_value = area.hash_value
 AND w.event NOT IN ('pipe get', 'PL/SQL lock timer')
 AND status = 'ACTIVE'
 AND TYPE != 'BACKGROUND'
 AND s.SID = w.SID
 order by s.USERNAME, w.EVENT;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: