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.
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!