How to find out Which User is Running what SQL Query in Oracle database?
Updated on Sep 30, 2020
Do you wonder how to get information on all the active query in the Oracle database? Do you want to know what query is executed by which user and how long is it running? Here is how to do it!
Oracle Current Activity
Given below is a small query that provides the following information about current activity in Oracle database
- Which user is currently logged-on?
- Which SQL Query are they running?
- Which computer the user is logged on from?
- How long the query is running?
Pre-requisite: What privilege do you need?
Generally you need SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY grant. Alternatively, if you have SELECT grant on v$session and v$sqlarea, then also you are fine.
SQL Query
SELECT
SUBSTR(SS.USERNAME,1,8) USERNAME,
SS.OSUSER "USER",
AR.MODULE || ' @ ' || SS.MACHINE CLIENT,
SS.PROCESS PID,
TO_CHAR(AR.LAST_LOAD_TIME, 'DD-Mon HH24:MM:SS') LOAD_TIME,
AR.DISK_READS DISK_READS,
AR.BUFFER_GETS BUFFER_GETS,
SUBSTR(SS.LOCKWAIT,1,10) LOCKWAIT,
W.EVENT EVENT,
SS.STATUS,
AR.SQL_fullTEXT SQL
FROM V$SESSION_WAIT W,
V$SQLAREA AR,
V$SESSION SS,
v$TIMER T
WHERE SS.SQL_ADDRESS = AR.ADDRESS
AND SS.SQL_HASH_VALUE = AR.HASH_VALUE
AND SS.SID = W.SID (+)
AND SS.STATUS = 'ACTIVE'
AND W.EVENT != 'client message'
ORDER BY SS.LOCKWAIT ASC, SS.USERNAME, AR.DISK_READS DESCHope you find this query useful!
