See SQL Being Executed for SID

----------------------------------------------------
-- Script: see_sql_for_sid.sql
-- Author: Michael Messina
-- Parameters: SID
-- Description: See SQL Text executing for SID
--------------------------------------------------
set long 4000

-- 10g and above
select sql_fulltext
from v$sql
WHERE (address, hash_value) IN
(SELECT sql_address, sql_hash_value
FROM v$session WHERE sid = &1);

Example:

SQL> @see_sql_for_sid.sql 139
old 6: WHERE sid = &1)
new 6: WHERE sid = 139)

SQL_FULLTEXT
--------------------------------------------------------------------------------
select sql_fulltext
from v$sql
WHERE (address, hash_value) IN
(SELECT sql_address, sql_hash_value
FROM v$session
WHERE sid = 139)

1 row selected.