What is AUDSID June 17, 2010
Posted by mwidlake in internals.Tags: audit, data dictionary, performance
12 comments
If you look at v$session you sid SID and SERIAL#, which most of us DBA-types know uniquely identify a session and allow you to kill it (*with the relevant permissions and knowledge you are not about to compromise a business process).
But what is AUDSID?
desc v$session Name Null? Type ----------------------------------------- -------- ------------ SADDR RAW(8) SID NUMBER SERIAL# NUMBER AUDSID NUMBER PADDR RAW(8) USER# NUMBER USERNAME VARCHAR2(30) COMMAND NUMBER OWNERID NUMBER TADDR VARCHAR2(16) LOCKWAIT VARCHAR2(16) STATUS VARCHAR2(8) SERVER VARCHAR2(9) SCHEMA# NUMBER SCHEMANAME VARCHAR2(30) OSUSER VARCHAR2(30) ...
AUDSID is a unique identifier for the session and is used in sys.aud$ , as the SESSIONID column. It is the leading column of the only index on sys.aud$
IND_OWNER IND_NAME TAB_NAME PSN COL_NAME
----------- ------------------ ------------------ --------- ------------
SYS I_AUD1 AUD$ 1 SESSIONID
2 SES$TID
All audit records for a session are recorded with the same SESSIONID/AUDSID. Activity can be audited at session level and statement level. If audited at session level, only a single record for that audited thing is recorded in the log. This is updated to indicate any new audited actions that occur on that thing for the duration of the session, so it is important that Oracle can efficiently identify and update that record, as the impact of SQL AUDIT on database functionality wants to be kept to a minimum.
How does AUDSID relate to SID, SERIAL#, USERNAME etc? Let’s have a quick look.
select sid, serial# ,audsid ,username,OSUSER,schemaname
from v$session
SID SERIAL# AUDSID USERNAME OSUSER SCHEMANAME
---------- ---------- ---------- -------------------- ------------------------- --------------
485 4745 165550 LEX_AAAAAEX oracle LEX_AAAAAEX
487 23712 165546 LEX_AAAAAEX oracle LEX_AAAAAEX
497 40388 0 oracle SYS
498 19269 0 oracle SYS
502 13362 165432 EAAAAA govanii EAAAAA
505 407 163821 LEXAAAAA oracle LEXAAAAA
506 6302 4294967295 SYS widlake SYS
511 11702 165518 OAAAAA backerella OAAAAA
512 17076 165490 LEX_AAAAAEX oracle LEX_AAAAAEX
518 9066 165507 OAAAAA Manoled OAAAAA
519 6956 163976 LEX_AAAAAEX oracle LEX_AAAAAEX
521 15272 4294967295 SYS widlake SYS
523 4825 163975 LEX_AAAAAEX oracle LEX_AAAAAEX
536 63941 4294967295 SYS backerella SYS
524 19740 165548 LEX_AAAAAEX oracle LEX_AAAAAEX
525 850 165549 LEX_AAAAAEX oracle LEX_AAAAAEX
526 45112 165516 XXX backerella XXX
527 11086 163963 LEX_AAAAAEX oracle LEX_AAAAAEX
529 1662 163818 LEX_AAAAA oracle LEX_AAAAA
530 62788 4294967295 SYS widlake SYS
537 1 0 oracle SYS
540 1 0 oracle SYS
It does not. But it is interesting to note that this database does not have auditing enabled but the AUDSID is still populated.
It is also interesting that all SYS accounts have either an AUDSID of 0 or the same AUDSID, 4294967295 {which is 1 less than 4294967295…1024*1024*4}. I might come back to that shared AUDSID at a later date…
All the “normal” AUDSIDs are unique and about the same value, 163900 to 165560.
If you look at sys.aud$ the SESSIONID increments as time goes on. By this I do not mean it is in order of SESSIONID, it is not, but as time goes by the SESSIONID increments. That sounds a lot like what you would see with a sequence to me. And in fact that is because the value IS from a sequence:
select * from dba_sequences where sequence_name = 'AUDSES$' SEQUENCE_OWNER SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY cycle O CACHE_SIZE LAST_NUMBER --------------- --------------- ---------- ---------- ------------ ----- - ---------- ----------- SYS AUDSES$ 1 2000000000 1 Y N 10000 173695
So the AUDSID is a unique value that comes from a sequence and is used in the audit trail. All SYS accounts have the same AUDSID or have it set to zero.
Why do many sessions have the AUDSID set to zero? Because they are internal Oracle processes:
select sid, serial# ,audsid ,username,OSUSER,schemaname,program from v$session where AUDSID = 0 SID SERIAL# AUDSID USERNAME OSUSER SCHEMANAME PROGRAM ----- ---------- ---------- ------------ ------------ ------------ ---------------------------- 506 6887 0 oracle SYS oracle@db25.eng.ham.uk. (q001) 526 45682 0 oracle SYS oracle@db25.eng.ham.uk. (q002) 531 91 0 YYYYY oracle DLPP1 oracle@db25.eng.ham.uk. (J000) 533 3 0 oracle SYS oracle@db25.eng.ham.uk. (CJQ0) 537 1 0 oracle SYS oracle@db25.eng.ham.uk. (q000) 540 1 0 oracle SYS oracle@db25.eng.ham.uk. (QMNC) 546 1 0 oracle SYS oracle@db25.eng.ham.uk. (MMNL) 547 1 0 oracle SYS oracle@db25.eng.ham.uk. (MMON) 548 1 0 oracle SYS oracle@db25.eng.ham.uk. (RECO) 549 1 0 oracle SYS oracle@db25.eng.ham.uk. (SMON) 550 1 0 oracle SYS oracle@db25.eng.ham.uk. (CKPT) 551 1 0 oracle SYS oracle@db25.eng.ham.uk. (LGWR) 552 1 0 oracle SYS oracle@db25.eng.ham.uk. (DBW0) 553 1 0 oracle SYS oracle@db25.eng.ham.uk. (MMAN) 554 1 0 oracle SYS oracle@db25.eng.ham.uk. (PSP0) 555 1 0 oracle SYS oracle@db25.eng.ham.uk. (PMON)
The activity of PMON, SMON, CKPT is not audited of course. And job scheduler doing nothing do not have their activity audited either.
Oh, you remember I said AUDSID was unique? Well, check back at the sequence. It wraps and has a maximum value of 2,000,000,000. It is going to start repeating values after 2 billion sessions. Make a noter to ensure you have cleaned up your audit trail before you hit your 2 billion and first session…
–
