How to check session details and SQL text for a given SID in Oracle

Give a SID; get the full session context — OS PID, machine, wait event, SQL_ID — plus the SQL text from V$SQLTEXT_WITH_NEWLINES. Falls back to the previous SQL when the session''s current SQL address is null.

SET LINES 200 PAGES 1000 FEED OFF ECHO OFF VER OFF
COL sid       FORMAT 9999
COL serial#   FORMAT 9999999
COL spid      FORMAT a7
COL username  FORMAT a11
COL osuser    FORMAT a10
COL status    FORMAT a8
COL sql_text  FORMAT a100
COL program   FORMAT a15
COL machine   FORMAT a16
COL event     FORMAT a30
COL wait_class FORMAT a20

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YY HH24:MI';

ACCEPT sid PROMPT 'Enter SID: '

SELECT s.sid, s.serial#, p.spid, s.username, s.osuser, s.status,
       s.logon_time, s.program, s.machine, SYSDATE AS now,
       event, wait_class, wait_time, seconds_in_wait, state, sql_id
FROM   v$session s, v$process p
WHERE  s.paddr = p.addr
AND    s.sid   = &&sid;

SELECT piece, REPLACE(sql_text, CHR(10)) AS sql_text
FROM   v$sqltext_with_newlines
WHERE  address = (
         SELECT DECODE(RAWTOHEX(s.sql_address),
                       '00', s.prev_sql_addr,
                       NULL, s.prev_sql_addr,
                       s.sql_address)
         FROM   v$session s, v$process p
         WHERE  p.addr = s.paddr
         AND    s.sid  = &sid
       )
ORDER BY piece;

SET FEED ON