How to check what SQL a session is running in Oracle

Give a SID; get the SQL_ID it is running now, the previous SQL_ID, and the full SQL text for both — handy when troubleshooting a single misbehaving session.

SET LONG 200000 PAGES 100 LINES 200
COL sql_text FORMAT A120

ACCEPT p_sid NUMBER PROMPT 'Enter SID: '

SELECT s.sid, s.serial#, s.username, s.status, s.machine, s.program,
       s.sql_id, s.prev_sql_id
FROM   v$session s
WHERE  s.sid = &&p_sid;

SELECT 'CURRENT'  AS which, sql_id, sql_fulltext AS sql_text
FROM   v$sql
WHERE  sql_id = (SELECT sql_id      FROM v$session WHERE sid = &p_sid)
UNION ALL
SELECT 'PREVIOUS', sql_id, sql_fulltext
FROM   v$sql
WHERE  sql_id = (SELECT prev_sql_id FROM v$session WHERE sid = &p_sid);