Audit Trigger Fails



We recently added CFR audit for 20 tables on ur XE 7333 installation.

One of the tables F3112 has an issue when R31422 runs, the logs show that it happens during the CFR Update trigger execution.
The pointed line is the following query:

select program into programid from v$session where audsid = userenv('sessionid') and user in (select user from dual);

THis does not happen when user submits the job, it happens on scheduled runs only.

Does anyone have an insight into how the Sessions are generated in the DB that could cause this problem?
Looking at the query, audsid shouldl be unique, and there is no reason it would pickup more than 1 record. So, somehow AUDSID duplicates.
(error is, indeed, that the query fetches more than 1 line. trigger does not have exception handling).


Legendary Poster

It might be handy to know what your database is.

Do you run run two or more jobs on the scheduler at the same time, using the same user ID?


VIP Member

Based on the query you have posted you are running on Oracle. I was surprised you could get two records for a query based on userenv('sessionid') so I did a bit of research. It seems that AUDSID in V$SESSION can be 0 when connecting as an internally generated SYS session or on some older Oracle releases when AUDIT TRAIL is not turned on. But by older version I mean Oracle 7 and below which even with XE I don't think was ever supported. Further, I would not expect JDE to be establishing a connect internal session unless there is a script running with CONNECT / AS SYSDBA or similar. A UBE would certainly not be doing that. (?)

See "How Sessions get Their AUDSID Identifier (Doc ID 122230.1)" on Oracle support. AUDSID's should be unique for the internal connections which will be 0 or UB4MAX(4294967295) for direct SYS connections. The sequence that the AUDSID is taken from is SYS.AUDSES$ which wraps at 2,000,000,000 but that also couldn't account for duplicated AUDSID.

So unless the scheduled job is somehow connecting as SYSDBA I don't see how there could be duplicate rows for that query. If this is reproducible in your non-production environment I would probably modify the trigger to issue a query like:

select sid, audsid, user from v$session where audsid = userenv('sessionid') and user in (select user from dual);

and write out the results through a separate function running an AUTONOMOUS TRANSACTION so that you can log into a table what records are actually coming back without that logging insert being rolled back when the trigger errors.


Thanks for the replies.

I was not able to replicate the issue in other environments.
It is kind of hard to understand if it is running as SYS, i would assume that in that case, every execution would error out for any UBE that touches a table being audited.

I did read about the Sessions in the database, and i agree there should never be duplicated except 0 or UB4MAX.

I would belive that:
A) something is wrong on a database level
B) A setup from JDE is not properly done for auditing F3112 specifically

DBA have not been especially helpful and they keep saying it is an expected error given that query, and that query should be changed