-- name       : sql.sql
-- date       : Jul-2001
-- Author     : Pentest Limited
-- Description: Get the sql someone is running in another database session
-- limitation : need to have select privilege on v_$sqltext and v_$sqlarea
--
-- useage     : SQL> connect username/password @sql.sql [SID] [serial]


undefine usersid
undefine userserial

col hash_value noprint
break on hash_value skip 1 nodup

col sql_text for a64 head 'SQL Code'
set lines 132 pause off

select 	sqla.hash_value,
	sqlt.sql_text
from 	v$session sess,
	v$sqlarea sqla,
	v$sqltext sqlt,
	v$process proc
where sess.username is not null
and proc.addr = sess.paddr
and sess.audsid != userenv('SESSIONID')
and sess.sql_address = sqla.address
and sess.sql_hash_value = sqla.hash_value
and sqla.address = sqlt.address
and sqla.hash_value = sqlt.hash_value
and sess.sid='&&usersid'
and sess.serial#='&&userserial'
order by sess.last_call_et desc,
	  sqla.address,
	sqla.hash_value,
	sqlt.piece;
clear breaks