Join v$session, v$sql and v$open_cursor : open_cursor « System Tables Views « Oracle PL / SQL






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>

 








Related examples in the same category