-- @pid_opencursors_sqltext.sql
SET echo off
SET lines 200 
SET PAGES 50

COLUMN SPID FORMAT a6
COLUMN SID FORMAT 999
COLUMN SERIAL# FORMAT 99999
COLUMN username FORMAT a10
COLUMN osuser FORMAT a10
COLUMN machine FORMAT a20
COLUMN program FORMAT a20
COLUMN sql_text FORMAT a30


select LPAD(p.sPID,5,' ') SPID
     , s.SID
     , s.SERIAL#
     , s.username
     , s.osuser
     , TO_CHAR(s.LOGON_TIME,'DD-MM-YY HH24:MI') logon_time
     , s.MACHINE
     , s.PROGRAM
     , sql.piece
     , sql.sql_text
from v$session s, v$process p, v$open_cursor c, v$sqltext sql
where s.PADDR = p.ADDR
AND   p.spid = '&PID'
and   s.sid = c.sid
and   c.address = sql.address
and   c.hash_value = sql.hash_value
order by spid, c.address, c.hash_value, piece
;


Prompt "#######  Current SQL being executed ######################"

SELECT sql.piece, sql.sql_text
FROM   v$sqltext_with_newlines sql, v$session s, v$process p
where s.PADDR = p.ADDR
AND   s.SQL_ADDRESS = sql.address
AND   s.SQL_HASH_VALUE = sql.hash_value
AND   p.spid = '&&PID'
order by sql.piece
/


SET echo on