Question:

Source=MyOracleDB;User

Id=test;Password=test;WSID=test"

We want to read WSID from this string in backend session for the purpose of Auditing using triggers. Is there any means of reading this information (WSID) in backend? WSID= Workstation ID

This question posed on 24 January 2006

Answer:

You may want to look at the connection data inside Oracle:

column username format a12 heading 'User Name'

column program format a25 heading 'Executing|Program'

column pid format 9999

column sid format 9999

column osuser heading Operating|system|User format a10

column terminal format a10

column g_date new_value f_date noprint

select to_char(sysdate,'ddmmyyyyhh24mi') g_date from dual;

set lines 130

break on username

compute count of pid on username

set pages 58

start title132 "Oracle Processes"

spool rep_out\&db\cur_proc_&&f_date

select

      NVL(a.username,'Null') username,

      b.pid,

      a.sid,

      decode(a.terminal,'?','Detached',a.terminal) terminal,

      b.program,

        spid,

      a.osuser,

      a.serial#

 from

      v$session a,

      v$process b

where

      a.PADDR = b.ADDR

order by

      a.username,

      b.pid

/

spool off

clear breaks

clear columns

Operating

                                                        Executing                             
system
User Name     PID   SID    TERMINAL        Program          SPID       User 
SERIAL#

------------ ----- ----- ---------- ------------------ ------- -----------

OEMMAN       14      15    MRALAPTOP2    ORACLE.EXE    2496         SYSTEM              7

                  15       14   MRALAPTOP2    ORACLE.EXE    3336         SYSTEM              8

                  16       16   MRALAPTOP2    ORACLE.EXE    3908         SYSTEM            174

                  17       11   MRALAPTOP2    ORACLE.EXE    3732         SYSTEM             14

                  18       12   MRALAPTOP2    ORACLE.EXE    132          SYSTEM             33

                  19       18   MRALAPTOP2    ORACLE.EXE    300          SYSTEM              6

                  20       19   MRALAPTOP2    ORACLE.EXE    604          SYSTEM              6

                  21       20   MRALAPTOP2    ORACLE.EXE    976          SYSTEM             12

                  22       21   MRALAPTOP2    ORACLE.EXE    4152         SYSTEM             13

************ -----
count            9

SYSTEM          23    22   MRALAPTOP2    ORACLE.EXE    2688         Mike?Ault        2851

************ -----

count            1

Null               2     1      MRALAPTOP2     ORACLE.EXE    2904         SYSTEM            1

                   3     2       MRALAPTOP2     ORACLE.EXE    2976         SYSTEM            3

                   4     3       MRALAPTOP2     ORACLE.EXE    2940         SYSTEM            1

                   5     4       MRALAPTOP2     ORACLE.EXE    2956         SYSTEM            1

                   6     5       MRALAPTOP2     ORACLE.EXE    2900         SYSTEM              1

                   7     6       MRALAPTOP2     ORACLE.EXE    2924         SYSTEM           1

                   8     7       MRALAPTOP2     ORACLE.EXE    2500         SYSTEM          1

                   9     8       MRALAPTOP2     ORACLE.EXE    2604         SYSTEM           1

                  12     9       MRALAPTOP2     ORACLE.EXE    972          SYSTEM           1

                  13    10      MRALAPTOP2      ORACLE.EXE    2608         SYSTEM           1

************ -----
count           10