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.