Tricky! Getting current OSUSER from V$SESSION

[gopi]

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.

[pando]

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]

[JChiappa]

Pando, just to know : what is the problem with SYS, in what version ? To me (in the versions what I have) all looks correct :

---------------------------------------------------
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

[pando]

in 9.2 you always get 0 as audsid

[JChiappa]

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