This discussion is archived
2 Replies Latest reply: Mar 28, 2013 7:50 AM by CSM.DBA RSS

Question on USER_NAME column in V$OPEN_CURSOR

CSM.DBA Journeyer
Currently Being Moderated
Hi All,

In V$OPEN_CURSOR the USER_NAME column is the "User that is logged in to the session" a/c to the documentation.
SQL> select * from v$open_cursor where *USER_NAME='SH'*;

SADDR                   SID USER_NAME                      ADDRESS          HASH_VALUE SQL_ID        SQL_TEXT                                                     LAST_SQL_ SQL_EXEC_ID CURSOR_TYPE
---------------- ---------- ------------------------------ ---------------- ---------- ------------- ------------------------------------------------------------ --------- ----------- ----------------------------------------------------------------
0000000097BBCAF8         33 SH                             000000008C186458 4087094668 g4y6nw3tts7cc BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END;                              DICTIONARY LOOKUP CURSOR CACHED
0000000097BBCAF8         33 SH                             000000008E3E0EE8 3933222116 dyk4dprp70d74 SELECT DECODE('A','A','1','2') FROM DUAL                                           DICTIONARY LOOKUP CURSOR CACHED
0000000097BF8418         13 SH                             000000008C0A91E0  649132870 gm9t6ycmb1yu6 delete from smon_scn_time where scn =  (select min(scn) from                       SESSION CURSOR CACHED
0000000097BBCAF8         33 SH                             000000008C3B0178  914163366 4vs91dcv7u1p6 insert into sys.aud$( sessionid,entryid,statement,ntimestamp                       OPEN-RECURSIVE
0000000097BBCAF8         33 SH                             000000008C22CF20  225524178 d6vwqbw6r2ffk SELECT USER FROM DUAL                                                              DICTIONARY LOOKUP CURSOR CACHED
0000000097BF8418         13 SH                             000000008C07A960 4177627317 dma0vxbwh325p update smon_scn_time set time_mp=:1, time_dp=:2, scn=:3, scn                       SESSION CURSOR CACHED
0000000097BBCAF8         33 SH                             000000008C05A850  616533857 cw6vxf0kbz3v1 SELECT CHAR_VALUE FROM SYSTEM.PRODUCT_PRIVS WHERE   (UPPER('                       DICTIONARY LOOKUP CURSOR CACHED
0000000097BBCAF8         33 SH                             000000008C3DF6F0 4253530419 7hys3h7ysgf9m SELECT ATTRIBUTE,SCOPE,NUMERIC_VALUE,CHAR_VALUE,DATE_VALUE F                       DICTIONARY LOOKUP CURSOR CACHED
0000000097BBCAF8         33 SH                             0000000091B6D7D8 2212873362 cwnvyjq1ybj4k table_1_ff_208_0_0_0                                                               OPEN
0000000097BAAD08         *39 SH                             0000000091B4B788  864012087 96g93hntrzjtr select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt,                       BUNDLE DICTIONARY LOOKUP CACHED*

10 rows selected.
But it reality
SQL> select sid,username from v$session where sid in (13,33,39);

       SID USERNAME
---------- ------------------------------
        13
        33 SH
        *39 SYS*
SID-39 is the SYS user but why it's showing that for the user "SH"? If Oracle's intention is to show all the sessions which are working on something(SYS is issuing the select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_c...) because of this session (SH), Why it's not mentioned clearly in the documentation. It just says "User that is logged in to *the session*"

CSM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points