2 Replies Latest reply on Mar 28, 2013 2:50 PM by CSM.DBA

    Question on USER_NAME column in V$OPEN_CURSOR

    CSM.DBA
      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