Oracle PL/SQL/System Tables Views/open cursor
Join v$session, v$sql and v$open_cursor
SQL>
SQL>
SQL> SELECT SUBSTR(USERNAME,1,10) "User",
2 SUBSTR(machine,1,10) "Machine",
3 sharable_mem, persistent_mem,
4 runtime_mem, executions, v$sql.module,
5 SUBSTR(v$sql.sql_text,1,60) "Statement"
6 FROM v$session, v$sql, v$open_cursor
7 WHERE v$open_cursor.saddr = v$session.saddr
8 AND v$open_cursor.address = v$sql.address
9 and rownum < 50
10 ORDER BY SUBSTR(USERNAME,1,10), SUBSTR(machine,1,10);
User Machine SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM EXECUTIONS MODULE Statement
---------- ---------- ------------ -------------- ----------- ---------- ---------------------------------------------------------------- ------------------------------------------------------------
J 8591 2456 1876 11 select priority from resource_mapping_priority$ where attrib
J 21249 5972 4312 384 select name,intcol#,segcol#,type#,length,nvl(precision#,0),d
J 8601 2824 2184 10 select value, consumer_group from resource_group_mapping$ wh
J 17003 4956 3736 949 select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oi
J 21249 5972 4312 384 select name,intcol#,segcol#,type#,length,nvl(precision#,0),d
J 29586 11084 8400 322 select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t
J 8588 2776 2196 10 select count(*) from resource_group_mapping$ where attribute
J 12702 2948 2128 244 select con#,obj#,rcon#,enabled,nvl(defer,0) from cdef$ where
Page 1
User Machine SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM EXECUTIONS MODULE Statement
---------- ---------- ------------ -------------- ----------- ---------- ---------------------------------------------------------------- ------------------------------------------------------------
J 12754 5636 4836 401 select /*+ rule */ bucket, endpoint, col#, epvalue from hist
J 12754 5636 4836 401 select /*+ rule */ bucket, endpoint, col#, epvalue from hist
J 12776 3052 2172 394 select pos#,intcol#,col#,spare1,bo#,spare2 from icol$ where
J 12824 3632 2888 12 select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$ t
J 39146 14932 12160 318 select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.fla
J 22400 5324 4644 898 update sys.col_usage$ set equality_preds = equality_pre
J 16951 4392 3032 244 select con#,type#,condlength,intcols,robj#,rcon#,match#,refa
J 17003 4956 3736 949 select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oi
Page 2
User Machine SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM EXECUTIONS MODULE Statement
---------- ---------- ------------ -------------- ----------- ---------- ---------------------------------------------------------------- ------------------------------------------------------------
J 12860 3500 2740 286 select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0
J 12878 3468 2708 286 select col#, grantee#, privilege#,max(mod(nvl(option$,0),2))
J 21249 5972 4312 384 select name,intcol#,segcol#,type#,length,nvl(precision#,0),d
J 12825 3492 2492 878 select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,
J 29586 11084 8400 322 select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t
J 16939 4656 3156 3582 select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt,
J 16939 4656 3156 3582 select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt,
J 12694 2856 2176 239 insert into sys.mon_mods$ values (:1, :2, :3, :4, :5, :6, :7
Page 3
User Machine SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM EXECUTIONS MODULE Statement
---------- ---------- ------------ -------------- ----------- ---------- ---------------------------------------------------------------- ------------------------------------------------------------
J 12851 4196 3516 29 insert into smon_scn_time (thread, time_mp, time_dp, scn, sc
J 12719 4212 3532 1 insert into smon_scn_time (thread, time_mp, time_dp, scn, sc
J 18031 4640 3960 406 update sys.mon_mods$ set inserts = inserts + :ins, updates =
J 18041 3524 2804 105 insert into sys.col_usage$ values ( :objn, :coln, decode
J 17119 4200 2920 4 select local_tran_id, global_tran_fmt, global_oracle_id, glo
J 12776 3052 2172 394 select pos#,intcol#,col#,spare1,bo#,spare2 from icol$ where
J 39146 14932 12160 318 select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.fla
J 17003 4956 3736 949 select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oi
Page 4
User Machine SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM EXECUTIONS MODULE Statement
---------- ---------- ------------ -------------- ----------- ---------- ---------------------------------------------------------------- ------------------------------------------------------------
J 21249 5972 4312 384 select name,intcol#,segcol#,type#,length,nvl(precision#,0),d
J 12825 3492 2492 878 select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,
J 29586 11084 8400 322 select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t
J 16939 4656 3156 3582 select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt,
J 12754 5636 4836 401 select /*+ rule */ bucket, endpoint, col#, epvalue from hist
J 12668 3648 3128 47 delete from access$ where d_obj#=:1
J 12735 3412 2352 14 select increment$,minvalue,maxvalue,cycle#,order$,cache,high
J 12776 3052 2172 394 select pos#,intcol#,col#,spare1,bo#,spare2 from icol$ where
Page 5
User Machine SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM EXECUTIONS MODULE Statement
---------- ---------- ------------ -------------- ----------- ---------- ---------------------------------------------------------------- ------------------------------------------------------------
J 12878 3352 2632 1313 select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1
J 12802 2288 1728 1299 select count(*) from sys.job$ where (next_date > sysdate) an
J 17910 4872 4172 7 update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#
J 39146 14932 12160 318 select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.fla
J 73054 21536 20276 13 select OBJOID, CLSOID, RUNTIME, PRI, JOBTYPE, SCHLIM, WT,
J 12760 2864 2244 64 select count(*) from sys.job$ where next_date < :1 and (fiel
J 17003 4956 3736 949 select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oi
J 12860 3500 2740 286 select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0
Page 6
User Machine SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM EXECUTIONS MODULE Statement
---------- ---------- ------------ -------------- ----------- ---------- ---------------------------------------------------------------- ------------------------------------------------------------
J 39146 14932 12160 318 select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.fla
Page 7
49 rows selected.
SQL>
SQL>
SQL>
SQL>