see http://www.jlcomp.demon.co.uk/guilty.html

rem
rem Script: sinner.sql
rem Author: J.P.Lewis
rem Last Update: 01-June-1998
rem Purpose: Get recent SQL Text and Cost for a Unix PID
rem
rem Input variables:
rem Unix process id (of a PQ slave or oracle{SID} process)
rem
rem Usage:
rem start sinner {UNIX pid}
rem start sinnger 28120
rem
rem Notes:
rem For performance reasons the code runs in steps rather then
rem using a simple join. (Apart from the v$session/process bit
rem where there are no useful pseudo-indexed columns).
rem
rem The use of UNION ALLs instead of a simple OR is for the same reason
rem

define m_pid=&1

clear breaks
clear columns
set verify off
set pagesize 22

column sql_address new_value m_sql_addr noprint
column sql_hash_value new_value m_sql_hash noprint format 9999999999999999
column prev_sql_addr new_value m_prev_addr noprint
column prev_hash_value new_value m_prev_hash noprint format 9999999999999999

column logon_time format a14

select
ses.sid, ses.username, ses.osuser,
to_char(ses.logon_time,'dd-mon hh24:mi') logon_time,
ses.sql_address, ses.sql_hash_value,
ses.prev_sql_addr, ses.prev_hash_value
from
v$session ses,
v$process pro
where ses.paddr = pro.addr
and pro.spid = &m_pid
;

column which format a9
break on which skip 1

rem ===================================================
rem
rem This gets the cost, use, and first 2,000 characters
rem
rem ===================================================

select
'Current' which,
executions,
parse_calls,
sorts,
buffer_gets,
disk_reads,
sql_text
from
v$sqlarea
where
hash_value = &m_sql_hash
and address = '&m_sql_addr'
UNION ALL
select
'Previous' which,
executions,
parse_calls,
sorts,
buffer_gets,
disk_reads,
sql_text
from
v$sqlarea
where
hash_value = &m_prev_hash
and address = '&m_prev_addr'
;

rem ========================
rem
rem This gets the whole text
rem
rem ========================

column piece noprint

select
'Current' which,
piece,
sql_text
from v$sqltext
where hash_value = &m_sql_hash
and address = '&m_sql_addr'
UNION ALL
select
'Previous' which,
piece,
sql_text
from v$sqltext
where hash_value = &m_prev_hash
and address = '&m_prev_addr'
order by
1,2
;