top of page
Find Session Id Running Specific Query
At times DBAs need to find or search for session details that are running a specific query inside database. Example, you might want to find out session ID that is running ALTER TABLE command.
Note: This query will give details only if the query is still running inside the database.
SET LINES 300
SET PAGES 999
COL SID FOR 99999
COL SER# FOR 9999999
COL OS_ID FOR A5
COL STATUS FOR A8
COL SQL_FULLTEXT FOR A60
SELECT
SES.SID,
SES.SERIAL# SER#,
SES.PROCESS OS_ID,
SES.STATUS,
SQL.SQL_FULLTEXT
FROM
V$SESSION SES,
V$SQL SQL,
V$PROCESS PRC
WHERE
SES.SQL_ID=SQL.SQL_ID AND
SES.SQL_HASH_VALUE=SQL.HASH_VALUE AND
SES.PADDR=PRC.ADDR AND
UPPER(SQL.SQL_FULLTEXT) LIKE UPPER('ALTER TABLE%SHRINK%');
You can change the last line to search for sessions that are running specific queries. Replace ALTER TABLE%SHRINK% with other command that you want to search.
bottom of page