Oracle-DB: Link between audit trail and active session history
Unfortunately the audit trail of the Oracle-DB uses a different session identifier (AudSid) than the Active Session History (SID + Serial#).
Both identifiers are available in v$Session (AudSid, SID, Serial#). So during lifetime of a session it is possible to link between the session info from v$Session an audit trail.
But neither the AudSID is stored in ASH (v$Active_Session_History) nor the SID + Serial# is stored in audit trail.
This prevents from combining session info of both sources after the session is closed.
The LOGOFF records in audit trail (if AUDIT SESSION is active) record also the Client_Identifier from v$Session.
So supplying v$Session.Client_Identifier with the needed info allows to retrieve it from DBA_Audit_Trail.Client_ID.
This logon trigger does it:
CREATE OR REPLACE TRIGGER Client_ID AFTER LOGON ON DATABASE -- Put unique session identifier into client-id to have it also in DBA_AUDIT_TRAIL.Client_ID for LOGOFF-records -- works than as missing link between Active Session History and Audit Trail -- Peter Ramm, OSP Dresden, 2021-01-05 BEGIN -- Use alternative public package instead of SYS_CONTEXT to get the serial# sys.DBMS_SESSION.Set_Identifier('SID = '||DBMS_DEBUG_JDWP.CURRENT_SESSION_ID||', Serial# = '||DBMS_DEBUG_JDWP.CURRENT_SESSION_SERIAL); END; /
Comments
Post a Comment