Show the contents of the SGA for a given SID and/or username. In the (truncated) example below a search has been run on a 2-instance RAC installation for SID 691.
SID 691 on instance 2 is the schema running the query (you can see the code in the open cursor records). On instance 1 SID 691 turned out to be SYS.
Enter user name or part thereof or none :
SID (enter 0 for all) : 691
INST_ID Username Sid Sql Text Open Comm. Type Program OS User SQL ID ADDRESS HASH_VALUE SQL_CHILD_NUMBER PIECE
---------- ------------------------- ----- ---------------------------------------------------------------- ----------- ---------- ------------------------------------------------ -------------------- ------------- ---------------- ---------- ---------------- ----------
1 SYS 691 select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t.int 3 oraagent.bin@clx46ap01 (TNS V1-V3) oracle 1gu8t96d0bdmu 000000011B954118 2584065658 0
1 SYS 691 cols,nvl(t.clucols,0),t.audit$,t.flags,t.pctfree$,t.pctused$,t.i 3 oraagent.bin@clx46ap01 (TNS V1-V3) oracle 1gu8t96d0bdmu 000000011B954118 2584065658 1
1 SYS 691 nitrans,t.maxtrans,t.rowcnt,t.blkcnt,t.empcnt,t.avgspc,t.chncnt, 3 oraagent.bin@clx46ap01 (TNS V1-V3) oracle 1gu8t96d0bdmu 000000011B954118 2584065658 2
1 SYS 691 t.avgrln,t.analyzetime,t.samplesize,t.cols,t.property,nvl(t.degr 3 oraagent.bin@clx46ap01 (TNS V1-V3) oracle 1gu8t96d0bdmu 000000011B954118 2584065658 3
1 SYS 691 ee,1),nvl(t.instances,1),t.avgspc_flb,t.flbcnt,t.kernelcols,nvl( 3 oraagent.bin@clx46ap01 (TNS V1-V3) oracle 1gu8t96d0bdmu 000000011B954118 2584065658 4
1 SYS 691 t.trigflag, 0),nvl(t.spare1,0),nvl(t.spare2,0),t.spare4,t.spare6 3 oraagent.bin@clx46ap01 (TNS V1-V3) oracle 1gu8t96d0bdmu 000000011B954118 2584065658 5
1 SYS 691 ,ts.cachedblk,ts.cachehit,ts.logicalread from tab$ t, tab_stats$ 3 oraagent.bin@clx46ap01 (TNS V1-V3) oracle 1gu8t96d0bdmu 000000011B954118 2584065658 6
1 SYS 691 ts where t.obj#= :1 and t.obj# = ts.obj# (+) 3 oraagent.bin@clx46ap01 (TNS V1-V3) oracle 1gu8t96d0bdmu 000000011B954118 2584065658 7
2 ROBERT1 691 INSERT INTO sqllog$ SELECT :1, 1+TRUNC(sqllog$_seq.nextval/8192) 2 sqlplusw.exe ROBERT1 01zk56t60wvf1 00000000F8FF35C0 1276014017 0 0
2 ROBERT1 691 FROM dual 2 sqlplusw.exe ROBERT1 01zk56t60wvf1 00000000F8FF35C0 1276014017 0 1
2 ROBERT1 691 SELECT signature FROM sqllog$ WHERE signature = :1 3 sqlplusw.exe ROBERT1 2uzqkdqdtkn8p 00000000F8FF4550 2610516245 0 0
2 ROBERT1 691 select unique vs.inst_id, vs.username "Username", Open cursor 3 sqlplusw.exe ROBERT1 9hwy0awdp6t1d 0000000102254ED0 458449965 0 0
2 ROBERT1 691 vs.sid "Sid", vst.sql_text "Sql Text", decode( vs. Open cursor 3 sqlplusw.exe ROBERT1 9hwy0awdp6t1d 0000000102254ED0 458449965 0 1
2 ROBERT1 691 sql_address, voc.address, 'Open cursor', '' ) "Open", vst Open cursor 3 sqlplusw.exe ROBERT1 9hwy0awdp6t1d 0000000102254ED0 458449965 0 2
2 ROBERT1 691 .command_type "Comm. Type", vs.program "Program", Open cursor 3 sqlplusw.exe ROBERT1 9hwy0awdp6t1d 0000000102254ED0 458449965 0 3
2 ROBERT1 691 vs.osuser "OS User", vst.sql_id "SQL ID", voc.addr Open cursor 3 sqlplusw.exe ROBERT1 9hwy0awdp6t1d 0000000102254ED0 458449965 0 4
2 ROBERT1 691 ess, voc.hash_value, vs.sql_child_number, v Open cursor 3 sqlplusw.exe ROBERT1 9hwy0awdp6t1d 0000000102254ED0 458449965 0 5
2 ROBERT1 691 st.piece from gv$session vs, gv$open_cursor voc, gv$sq Open cursor 3 sqlplusw.exe ROBERT1 9hwy0awdp6t1d 0000000102254ED0 458449965 0 6
2 ROBERT1 691 ltext vst where upper( vs.username ) like upper('%%' ) and vs. Open cursor 3 sqlplusw.exe ROBERT1 9hwy0awdp6t1d 0000000102254ED0 458449965 0 7
2 ROBERT1 691 sid = decode( '691', null, vs.sid, 0, vs.sid, '691' ) and voc. Open cursor 3 sqlplusw.exe ROBERT1 9hwy0awdp6t1d 0000000102254ED0 458449965 0 8
2 ROBERT1 691 inst_id = vs.inst_id and vst.inst_id = vs.inst_id and voc.si Open cursor 3 sqlplusw.exe ROBERT1 9hwy0awdp6t1d 0000000102254ED0 458449965 0 9
2 ROBERT1 691 d = vs.sid and vst.address = voc.address and vst.hash_value Open cursor 3 sqlplusw.exe ROBERT1 9hwy0awdp6t1d 0000000102254ED0 458449965 0 10
2 ROBERT1 691 = voc.hash_value order by vs.sid, vs.username, voc.address, vst Open cursor 3 sqlplusw.exe ROBERT1 9hwy0awdp6t1d 0000000102254ED0 458449965 0 11
2 ROBERT1 691 .piece Open cursor 3 sqlplusw.exe ROBERT1 9hwy0awdp6t1d 0000000102254ED0 458449965 0 12
2 ROBERT1 691 BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END; 47 sqlplusw.exe ROBERT1 9babjv8yq8ru3 000000010233DA50 1029988163 0 0
2 ROBERT1 691 insert into sys.aud$( sessionid,entryid,statement,ntimestamp#, u 2 sqlplusw.exe ROBERT1 4vs91dcv7u1p6 000000011A4E12F0 914163366 0 0
2 ROBERT1 691 serid,userhost,terminal,action#,returncode, obj$creator,obj$name 2 sqlplusw.exe ROBERT1 4vs91dcv7u1p6 000000011A4E12F0 914163366 0 1
2 ROBERT1 691 ,auth$privileges,auth$grantee, new$owner,new$name,ses$actions,se 2 sqlplusw.exe ROBERT1 4vs91dcv7u1p6 000000011A4E12F0 914163366 0 2
2 ROBERT1 691 s$tid,logoff$pread, logoff$lwrite,logoff$dead,comment$text,spare 2 sqlplusw.exe ROBERT1 4vs91dcv7u1p6 000000011A4E12F0 914163366 0 3
2 ROBERT1 691 1,spare2, priv$used,clientid,sessioncpu,proxy$sid,user$guid, in 2 sqlplusw.exe ROBERT1 4vs91dcv7u1p6 000000011A4E12F0 914163366 0 4
2 ROBERT1 691 stance#,process#,xid,scn,auditid, sqlbind,sqltext,obj$edition,d 2 sqlplusw.exe ROBERT1 4vs91dcv7u1p6 000000011A4E12F0 914163366 0 5
2 ROBERT1 691 bid) values(:1,:2,:3,SYS_EXTRACT_UTC(SYSTIMESTAMP), :4,:5,: 2 sqlplusw.exe ROBERT1 4vs91dcv7u1p6 000000011A4E12F0 914163366 0 6
2 ROBERT1 691 6,:7,:8, :9,:10,:11,:12, :13,:14,:15,:16,:17, :18,:1 2 sqlplusw.exe ROBERT1 4vs91dcv7u1p6 000000011A4E12F0 914163366 0 7
2 ROBERT1 691 9,:20,:21,:22, :23,:24,:25,:26,:27, :28,:29,:30,:31,:32, 2 sqlplusw.exe ROBERT1 4vs91dcv7u1p6 000000011A4E12F0 914163366 0 8
2 ROBERT1 691 :33,:34,:35,:36) 2 sqlplusw.exe ROBERT1 4vs91dcv7u1p6 000000011A4E12F0 914163366 0 9
2 ROBERT1 691 select timestamp, flags from fixed_obj$ where obj#=:1 3 sqlplusw.exe ROBERT1 asvzxj61dc5vs 000000011A6303F0 2195068792 0 0
2 ROBERT1 691 select node,owner,name from syn$ where obj#=:1 3 sqlplusw.exe ROBERT1 1mjd9xp80vuqa 000000011A633F30 1343089354 0 0
2 ROBERT1 691 SELECT obj_type, plan_id, name, flags, last_executed 3 sqlplusw.exe ROBERT1 47y3mqvyhpkvs 000000011A6436E8 4245343096 0 0
2 ROBERT1 691 FROM sqlobj$ 3 sqlplusw.exe ROBERT1 47y3mqvyhpkvs 000000011A6436E8 4245343096 0 1
2 ROBERT1 691 WHERE signature = :1 3 sqlplusw.exe ROBERT1 47y3mqvyhpkvs 000000011A6436E8 4245343096 0 2
2 ROBERT1 691 AND category = : 3 sqlplusw.exe ROBERT1 47y3mqvyhpkvs 000000011A6436E8 4245343096 0 3
2 ROBERT1 691 2 3 sqlplusw.exe ROBERT1 47y3mqvyhpkvs 000000011A6436E8 4245343096 0 4
2 ROBERT1 691 select col#,intcol#,ntab# from ntab$ where obj#=:1 order by intc 3 sqlplusw.exe ROBERT1 f3g84j69n0tjh 000000011A6B68C0 2470471216 0 0
2 ROBERT1 691 ol# asc 3 sqlplusw.exe ROBERT1 f3g84j69n0tjh 000000011A6B68C0 2470471216 0 1
2 ROBERT1 691 select intcol#, toid, version#, intcols, intcol#s, flags, synobj 3 sqlplusw.exe ROBERT1 9rfqm06xmuwu0 000000011A6B7120 3141366592 0 0
2 ROBERT1 691 # from subcoltype$ where obj#=:1 order by intcol# asc 3 sqlplusw.exe ROBERT1 9rfqm06xmuwu0 000000011A6B7120 3141366592 0 1
2 ROBERT1 691 select col#,intcol#,toid,version#,packed,intcols,intcol#s,flags, 3 sqlplusw.exe ROBERT1 7nuw4xwrnuwxq 000000011A6B7BC0 793605046 0 0
2 ROBERT1 691 synobj#, nvl(typidcol#, 0) from coltype$ where obj#=:1 order by 3 sqlplusw.exe ROBERT1 7nuw4xwrnuwxq 000000011A6B7BC0 793605046 0 1
2 ROBERT1 691 intcol# desc 3 sqlplusw.exe ROBERT1 7nuw4xwrnuwxq 000000011A6B7BC0 793605046 0 2
2 ROBERT1 691 select con#,obj#,rcon#,enabled,nvl(defer,0),spare2,spare3 from c 3 sqlplusw.exe ROBERT1 3w4qs0tbpmxr6 000000011A6DBB10 1465513702 0 0
2 ROBERT1 691 def$ where robj#=:1 3 sqlplusw.exe ROBERT1 3w4qs0tbpmxr6 000000011A6DBB10 1465513702 0 1
2 ROBERT1 691 select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2) 3 sqlplusw.exe ROBERT1 2q93zsrvbdw48 000000011A6DC380 4139184264 0 0
2 ROBERT1 691 )from objauth$ where obj#=:1 group by grantee#,privilege#,nvl(co 3 sqlplusw.exe ROBERT1 2q93zsrvbdw48 000000011A6DC380 4139184264 0 1
2 ROBERT1 691 l#,0) order by grantee# 3 sqlplusw.exe ROBERT1 2q93zsrvbdw48 000000011A6DC380 4139184264 0 2
2 ROBERT1 691 select text from view$ where rowid=:1 3 sqlplusw.exe ROBERT1 grwydz59pu6mc 000000011A6DD9A0 1398610540 0 0
2 ROBERT1 691 select cols,audit$,textlength,intcols,property,flags,rowid from 3 sqlplusw.exe ROBERT1 g3wrkmxkxzhf2 000000011A6DE720 1709162946 0 0
2 ROBERT1 691 view$ where obj#=:1 3 sqlplusw.exe ROBERT1 g3wrkmxkxzhf2 000000011A6DE720 1709162946 0 1
2 ROBERT1 691 select order#,columns,types from access$ where d_obj#=:1 3 sqlplusw.exe ROBERT1 8swypbbr0m372 000000011A6E4700 3993603298 0 0
2 ROBERT1 691 select rowcnt, blkcnt, empcnt, avgspc, chncnt, avgrln, analyzeti 3 sqlplusw.exe ROBERT1 afx304d90ps3z 000000011B7413A0 1376444543 0 0
2 ROBERT1 691 me, samplesize, avgspc_flb, flbcnt, flags from tab_stats$ where 3 sqlplusw.exe ROBERT1 afx304d90ps3z 000000011B7413A0 1376444543 0 1
2 ROBERT1 691 obj#=:1 3 sqlplusw.exe ROBERT1 afx304d90ps3z 000000011B7413A0 1376444543 0 2
2 ROBERT1 691 SELECT /*+ INDEX(sqlobj$ (signature category obj_type plan_id)) 3 sqlplusw.exe ROBERT1 f946p6b0qhh5t 000000011B7B7BB0 3244835001 0 0
2 ROBERT1 691 */ DISTINCT category 3 sqlplusw.exe ROBERT1 f946p6b0qhh5t 000000011B7B7BB0 3244835001 0 1
2 ROBERT1 691 FROM sqlobj$ 3 sqlplusw.exe ROBERT1 f946p6b0qhh5t 000000011B7B7BB0 3244835001 0 2
2 ROBERT1 691 WHERE signature = :1 3 sqlplusw.exe ROBERT1 f946p6b0qhh5t 000000011B7B7BB0 3244835001 0 3
-- Name : sga_user_sid.sql
prompt
prompt Purpose : Shows sql in the SGA for a given username/SID
prompt
-- Date last modified : 2015/09/29
--
-- Oracle Version: 11g
-- Other Oracle versions: should work on 9i and later
----------------------------------------------------------------------------------------
clear breaks
clear columns
col username for a25
col sid for 9999
col "OS User" for a20
prompt
spool sga_user_sid.rpt
accept usr_name prompt 'Enter user name or part thereof or none : '
prompt
accept xn_sid prompt "SID (enter 0 for all) : "
prompt
select unique vs.inst_id,
vs.username "Username",
vs.sid "Sid",
vst.sql_text "Sql Text",
decode( vs.sql_address, voc.address, 'Open cursor', '' ) "Open",
vst.command_type "Comm. Type",
vs.program "Program",
vs.osuser "OS User",
vst.sql_id "SQL ID",
voc.address,
voc.hash_value,
vs.sql_child_number,
vst.piece
from gv$session vs,
gv$open_cursor voc,
gv$sqltext vst
where upper( vs.username ) like upper('%&&usr_name%' )
and vs.sid = decode( '&&xn_sid', null, vs.sid, 0, vs.sid, '&&xn_sid' )
and voc.inst_id = vs.inst_id
and vst.inst_id = vs.inst_id
and voc.sid = vs.sid
and vst.address = voc.address
and vst.hash_value = voc.hash_value
order by vs.sid, vs.username, voc.address, vst.piece
/
spool off