--
--  Name: sqltext_active_sid.sql
--
--  Parms: 
--     #1: sid
--
--  Description: SQLText - Displays text of active SQLs for a specific sid
--
--  Compatability: 7.3.x and above
--
--  Author: 
--
--  Usage: @sqltext_active_sid 12
--
-- save SQL*Plus settings:
--store set savesqlplus_settings.sql replace
--
-- set SQL*Plus settings:
set linesize 133
set pages 40
--
column sql_address noprint 
column username format a12 
column osuser format a20
break on osuser on username on sid skip 1 
select osuser, username, sid, s.SQL_ADDRESS , sql_text
from v$sqltext a, v$session s 
where s.SQL_ADDRESS = a.ADDRESS 
and s.status = 'ACTIVE' 
and s.type !='BACKGROUND' 
and s.username is not null
and s.sid='&&1'
order by sid, a.ADDRESS, a.piece ;

column sql_address print 


--
-- restore SQL*Plus settings:
--@savesqlplus_settings