Oracle

How to get current running queries in Oracle database

Need to see current running queries in Oracle database

We can see all connected database sessions and its basic information by querying V$SESSION view, but we are able to only partial SQL statement (database query) in the view. For example, we only be able to see "SELECT" query is running, but cannot see full query string.

Let's get current running query of a database session and current running queries of all database sessions.

Get current running query of a database session

If we know which database session's query we need get already, we need to use "SID" in the following query.

※We can get SID from V$SESSION view. "SID = 123" in the following query needs to be what we get from V$SESSION view.

SELECT * FROM V$SESSION;

SELECT
  s.sid,
  s.serial#,
  s.status,
  s.machine,
  s.osuser,
  s.module,
  s.username,
  s.process,
  p.program,
  a.sql_text
FROM v$session s,
     v$sqlarea a,
     v$process p
WHERE s.PREV_HASH_VALUE = a.hash_value
  AND s.PREV_SQL_ADDR = a.address
  AND s.paddr = p.addr
  AND s.SID = 123
;

Get current running queries of all database sessions

Let's also get all current running queries of all database sessions as follows.

SELECT
  s.sid,
  s.serial#,
  s.status,
  s.machine,
  s.osuser,
  s.module,
  s.username,
  s.process,
  p.program,
  a.sql_text
FROM v$session s,
     v$sqlarea a,
     v$process p
WHERE s.PREV_HASH_VALUE = a.hash_value
  AND s.PREV_SQL_ADDR = a.address
  AND s.paddr = p.addr
  AND s.STATUS = 'ACTIVE'
;

If we have any database query issues or performance issue, we can look into each query by utilizing the queries.