Monday, February 21, 2011

How to view current active sessions in a Oracle DB

Below query can be used to view currently connected sessions in a Oracle DB.It is useful in situations where your DB is slow to response and you need to find out what sessions are exactly causing it.

SELECT USERNAME,SID,SERIAL#,TERMINAL,LOGON_TIME,STATUS,
(SELECT DISTINCT SQL_TEXT
FROM SYS.V_$SQL_SHARED_MEMORY
WHERE HASH_VALUE=SQL_HASH_VALUE)
FROM V$SESSION
WHERE STATUS='ACTIVE' AND
USERNAME IS NOT NULL

No comments:

Post a Comment