DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Tricky! Getting current OSUSER from V$SESSION

  1. #1
    Join Date
    Dec 2001
    Location
    Keene, NH
    Posts
    510
    How can I tell what the OSUSER is for my current session?

    This column is in v$session but I need to know which row in v$session is my own logon.

    I will be using this info in an AFTER LOGIN trigger.
    Don't blame me, I'm from Red Sox Nation.

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    select osuser
    from v$session
    where audsid=userenv('SESSIONID');

    dont try with user sys though

    this works as well
    select sys_context('USERENV', 'OS_USER') from dual;

    this works with sys

    [Edited by pando on 06-24-2002 at 05:02 PM]

  3. #3
    Join Date
    Sep 2000
    Location
    Sao Paulo,SP,Brazil, Earth, Milky Way
    Posts
    350
    Pando, just to know : what is the problem with SYS, in what version ? To me (in the versions what I have) all looks correct :

    Code:
    ---------------------------------------------------
    Connected to:
    Personal Oracle8i Release 8.1.7.0.0 - Production
    With the Partitioning option
    JServer Release 8.1.7.0.0 - Production
    
    sys@PO8I:SQL>column username format a20
    sys@PO8I:SQL>column osuser format a20
    sys@PO8I:SQL>select sid, serial#, username, osuser
      2 from v$session
      3 where audsid=userenv('SESSIONID');
      
    
           SID    SERIAL# USERNAME             OSUSER
    ---------- ---------- -------------------- --------------------
            11      40398 SYS                  JChiappa
    
    
      
    Connected to:
    Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
    With the Partitioning option
    JServer Release 8.1.7.0.0 - Production
    
    sys@EXAV:SQL>column username format a20
    sys@EXAV:SQL>column osuser format a20
    sys@EXAV:SQL>select sid, serial#, username, osuser
      2    from v$session
      3   where audsid=userenv('SESSIONID');
    
           SID    SERIAL# USERNAME             OSUSER
    ---------- ---------- -------------------- --------------------
            11      18980 SYS                  JChiappa
    
    sys@EXAV:SQL>
    
    Conectado a:
    Personal Oracle7 Release 7.3.4.0.0 - Production
    With the distributed, replication and parallel query options
    PL/SQL Release 2.3.4.0.0 - Production
    
    sys@PO7:SQL>column username format a20
    sys@PO7:SQL>column osuser format a20
    sys@PO7:SQL>select sid, serial#, username, osuser
      2    from v$session
      3   where audsid=userenv('SESSIONID');
    
          SID   SERIAL# USERNAME             OSUSER
    --------- --------- -------------------- --------------------
            8      2721 SYS                  JChiappa
    
    sys@PO7:SQL>
    
    Conectado a:
    Oracle7 Server Release 7.3.4.5.0 - Production
    With the distributed and parallel query options
    PL/SQL Release 2.3.4.5.0 - Production
    
    sys@TRAD:SQL>column username format a20
    sys@TRAD:SQL>column osuser format a20
    sys@TRAD:SQL>select sid, serial#, username, osuser
      2    from v$session
      3   where audsid=userenv('SESSIONID');
    
          SID   SERIAL# USERNAME             OSUSER
    --------- --------- -------------------- --------------------
           14      5799 SYS                  JChiappa

    Regards,

    Chiappa

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    in 9.2 you always get 0 as audsid

  5. #5
    Join Date
    Sep 2000
    Location
    Sao Paulo,SP,Brazil, Earth, Milky Way
    Posts
    350
    Thank you ! It´s a good thing to know, many scripts and programs of mine will have a bad time with it, then, when in 9i ...

    Regards,

    Chiappa

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width