RE: gv$session.audsid not unique for "normal" user session?

SQL> select INST_ID, SID, SERIAL#, AUDSID, PROGRAM, EVENT from gv$session where audsid = sys_context('USERENV','SESSIONID');

   INST_ID SID SERIAL# AUDSID PROGRAM
---------- ---------- ---------- ---------- ------------------------------------------------
EVENT

         1 2804 53710 1445320680 oracle_at_larry.corp.solutionary.com (PZ99) PX Deq: Execution Msg

         1 2853 47296 1445320680 sqlplus_at_larry.corp.solutionary.com (TNS V1-V3) PX Deq: reap credit

         2 3273 45272 1445320680 oracle_at_moe.corp.solutionary.com (PZ99) PX Deq: reap credit

3 rows selected.

The same query (without INST_ID) from V$SESSION (instead of gv$session) returns only one row.

Don Granaman

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Don Granaman Sent: Wednesday, February 09, 2011 3:04 PM To: oracle-l_at_freelists.org
Subject: gv$session.audsid not unique for "normal" user session?

I am trying to write an after logon trigger to capture some session information (PROGRAM, etc.) that can later be used in conjunction with audit trail info.

The idea is that SESSIONID (supposedly) uniquely identifies a session in AUD$ and AUDSID (generally, with a few well-known exceptions like AUDSID=0) uniquely identifies a session in GV$SESSION, so an after logon trigger should be able to capture this info and I should be able to correlate this captured info with later audit trail info via (essentially) AUD$.SESSIONID = GV$SESSION.AUDSID.

However, this one has me puzzled. None of these sessions are background processes or SYS logons. This is in a two node 10.2.0.4 RAC system (with AUDIT_TRAIL=DB,EXTENDED).

SQL> select inst_id, type, sid, serial#, audsid from gv$session where audsid = 1443323556;

   INST_ID TYPE SID SERIAL# AUDSID
---------- ---------- ---------- ---------- ----------

       1 USER       3032       6162 1443323556
       1 USER       3162      46368 1443323556
       2 USER       3221      48778 1443323556

3 rows selected.

Any clues as to why there are three records - in two instances?

Thanks in advance!
Don Granaman

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Fri Feb 11 2011 - 09:22:18 CST