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.

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.