Logo DWBI.org Login / Sign Up
Sign Up
Have Login?
This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
Login
New Account?
Recovery
Go to Login
By continuing you indicate that you agree to Terms of Service and Privacy Policy of the site.
Oracle

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

  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!

PrimeChess

PrimeChess.org

PrimeChess.org makes elite chess training accessible and affordable for everyone. For the past 6 years, we have offered free chess camps for kids in Singapore and India, and during that time, we also observed many average-rated coaches charging far too much for their services.

To change that, we assembled a team of top-rated coaches including International Masters (IM) or coaches with multiple IM or GM norms, to provide online classes starting from $50 per month (8 classes each month + 4 tournaments)

This affordability is only possible if we get more students. This is why it will be very helpful if you could please pass-on this message to others.

Exclucively For Indian Residents: 
Basic - ₹1500
Intermediate- ₹2000
Advanced - ₹2500

Top 10 Articles