Do you know how to get information on all the active query in the Oracle database?  Do you want to know what query is being executed by which user and how long is it running?

 Oracle Current Activity

Given below is a small query that provides the following information about current activity in Oracle database

  1. Which user is currently logged-on?
  2. Which SQL Query are they running?
  3. Which computer the user is logged on from?
  4. 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 DESC

Hope you find this query useful!