Oracle

How to get current running database query in Oracle

How to get current running query in Oracle

We can get database session information by querying V$SESSION view, but it doesn't show full query text. DBAs and developers sometimes need full query text in order to troubleshoot.

This article explains how to get full query text of a database session. We sometimes don't know which database session makes database performance issue, therefore this article also explains how to get all current running full query text.

Get current running full query text of a database session

If you already know which database session's current running query needs, please get SID value from V$SESSION view.

※Please use SID value which you got from V$SESSION view in the following query.

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 active database sessions

If you need current running queries of all active database sessions, please run the following query.

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 you encountered database performance issue or need to look into SQL query issue, you can get full query text by utilizing the above two queries.