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.