v$session.audsid

I am working on a project that requires me to get the name of the program during the logon process to the database.

"What an ideal place for a logon trigger!", I said to myself.

I thought the task would be quite simple, just get the SYS_CONTEXT('USERENV','SESSIONID') and use that to query v$session. I know that v$session.audsid is not unique as the background processes are "0" while other processes have a number that is unique for that moment in time. Or so I thought.

I tested my logon trigger like crazy in the development enviornment. Then I put it in the QA environment for a few days to make sure it was OK. No problems whatsoever during development or QA.

The first day I put my logon trigger in production, sure enough it failed. It threw a normal old PL/SQL error that indicated two rows were returned where a single row was expected.

Hmmm, that didn't happen in development.

After about 10 days of working on it here and there, I found that the threads of an RMAN backup all had the same v$session.audsid.

While just some monitoring stuff was running:

system@jh01.dev> l
1 select sid, serial#, username, program, audsid
2* from v$session where machine = 'barney'
SID SERIAL# USERNAME PROGRAM AUDSID
---------- ---------- ------------ ---------------------------------------- ----------
170 1 oracle@barney (PMON) 0
159 1 oracle@barney (CJQ0) 0
169 1 oracle@barney (PSP0) 0
168 1 oracle@barney (MMAN) 0
167 1 oracle@barney (DBW0) 0
166 1 oracle@barney (DBW1) 0
165 1 oracle@barney (DBW2) 0
164 1 oracle@barney (DBW3) 0
152 1 oracle@barney (ARC1) 0
153 1 oracle@barney (ARC0) 0
157 1 oracle@barney (MMNL) 0
158 1 oracle@barney (MMON) 0
160 1 oracle@barney (RECO) 0
162 1 oracle@barney (CKPT) 0
163 1 oracle@barney (LGWR) 0
161 1 oracle@barney (SMON) 0
139 65 JEFFH perl@barney (TNS V1-V3) 1600567
145 139 JEFFH perl@barney (TNS V1-V3) 1600566
143 47 JEFFH perl@barney (TNS V1-V3) 1600572
146 2 oracle@barney (q000) 0
149 1 oracle@barney (QMNC) 0
156 6 oracle@barney (q001) 0

While RMAN was running with two threads:
system@jh01.dev> /

SID SERIAL# USERNAME PROGRAM AUDSID
---------- ---------- ------------ ---------------------------------------- ----------
95 3229 SYS rman@barney (TNS V1-V3) 4294967295
170 1 oracle@barney (PMON) 0
158 1 oracle@barney (MMON) 0
169 1 oracle@barney (PSP0) 0
168 1 oracle@barney (MMAN) 0
167 1 oracle@barney (DBW0) 0
166 1 oracle@barney (DBW1) 0
165 1 oracle@barney (DBW2) 0
164 1 oracle@barney (DBW3) 0
163 1 oracle@barney (LGWR) 0
152 1 oracle@barney (ARC1) 0
153 1 oracle@barney (ARC0) 0
157 1 oracle@barney (MMNL) 0
159 1 oracle@barney (CJQ0) 0
160 1 oracle@barney (RECO) 0
162 1 oracle@barney (CKPT) 0
161 1 oracle@barney (SMON) 0
118 30350 SYS rman@barney (TNS V1-V3) 4294967295
145 139 JEFFH perl@barney (TNS V1-V3) 1600566
143 47 JEFFH perl@barney (TNS V1-V3) 1600572
139 65 JEFFH perl@barney (TNS V1-V3) 1600567
146 2 oracle@barney (q000) 0
149 1 oracle@barney (QMNC) 0
156 6 oracle@barney (q001) 0

Interesting?