— sid_sga.sql
— mdw 11/97
— pinched from ‘bee’ book
— list all sql in SGA attached to a given sid.

set pause on
set pages 24
set pause ‘Any Key>’
spool sid_sga.lst
select s.process fground
,p.spid bkgrnd,
x.disk_reads,
x.rows_processed,x.users_executing
,x.sql_text
from sys.v_$session s
,sys.v_$process p
,sys.v_$sqlarea x
where s.sid = &sid
and  s.paddr = p.addr
and s.type != ‘BACKGROUND’
and s.sql_address=x.address
and s.sql_hash_value = x.hash_value
order by s.sid
/
spool off
— EOF