SID stays same, AUDSID alters June 24, 2010
Posted by mwidlake in internals.Tags: data dictionary, sql*plus
trackback
In a couple of recent posts I described what AUDSID is and how you check out what you SID and AUSID are.
Well, your SID may be a little more stable than you think. In the below output you can see that as I use the SQL*Plus connect command to change users my AUDSID increases, new values being selected from the dictionary sequence, but my SID stays the same.
{the below is on 10.2.0.3, no RAC involved, using the windows SQL*Plus client. All usernames, passwords and sids are not real, to protect the innocent}.
1* SELECT USER,sys_context('USERENV','SESSIONID'),SYS_CONTEXT('USERENV','SID') FROM DUAL
TDB>
TDB> /
USER SYS_CONTEXT('USERENV','SESSIONID') SYS_CONTEXT('USERENV','SID')
----------- ---------------------------------- ----------------------------
DWPERF 253779 524
-- change user, same instance
TDB> CONNECT MDW/MDW@TDB
Connected.
-- {By the way, the information below, plus some I cut from the output, is from my logon.sql script}
you are utilising central dba scripts
altering the default date format to be dd-mon-yyyy hh24:mi
pause on, pages 24 head on recsep off
TDB> SELECT USER,sys_context('USERENV','SESSIONID'),SYS_CONTEXT('USERENV','SID') FROM DUAL
2 /
USER SYS_CONTEXT('USERENV','SESSIONID') SYS_CONTEXT('USERENV','SID')
----------- ---------------------------------- ----------------------------
MDW 253782 524
-- The SID is exactly the same. The AUDSID has increased by 3. This is a quiet dev/test system but
-- I am not the only user and my session had been alive for about 30 minutes,
-- so I would guess two other people logged on in that time
-- However, would you have predicted that the SID would remain the same?
-- I will connect back to the original user
TDB> CONNECT DWPERF/DWPERF@TDB
Connected.
you are utilising central dba scripts
altering the default date format to be dd-mon-yyyy hh24:mi
pause on, pages 24 head on recsep off
TDB> SELECT USER,sys_context('USERENV','SESSIONID'),SYS_CONTEXT('USERENV','SID') FROM DUAL
2 /
USER SYS_CONTEXT('USERENV','SESSIONID') SYS_CONTEXT('USERENV','SID')
----------- ---------------------------------- ----------------------------
DWPERF 253783 524
-- The SID stays the same still and the AUDSID goes up by one as it is a new session. No other new
-- sessions in the minute or so since I last changed accounts.
--
-- SYS is a different beast to all other accounts, so if I connect as SYS, will it force a new SID to be used?
TDB> CONNECT SYS/GUESS@TDB AS SYSDBA
Connected.
you are utilising central dba scripts
altering the default date format to be dd-mon-yyyy hh24:mi
pause on, pages 24 head on recsep off
TDB> SELECT USER,sys_context('USERENV','SESSIONID'),SYS_CONTEXT('USERENV','SID') FROM DUAL
2 /
USER SYS_CONTEXT('USERENV','SESSIONID') SYS_CONTEXT('USERENV','SID')
----------- ---------------------------------- ----------------------------
SYS 4294967295 524
-- That did surprise me. I thought that not only would I get an AUDSID set to that special value but
-- that Oracle would create a new session to ensure it was clean.
--
-- Maybe the persistence of the SID is some trick of SQL*Plus? It knows I have not really logged out
-- of the database and just clears pointers and memory constructs, so I will log into a second instance
-- and back...
TDB> connect dwperf/dwperf@TDB2
Connected.
you are utilising central dba scripts
altering the default date format to be dd-mon-yyyy hh24:mi
pause on, pages 24 head on recsep off
TDB2> SELECT USER,sys_context('USERENV','SESSIONID'),SYS_CONTEXT('USERENV','SID') FROM DUAL;
USER SYS_CONTEXT('USERENV','SESSIONID') SYS_CONTEXT('USERENV','SID')
----------- ---------------------------------- ----------------------------
DWPERF 233882 489
1 row selected.
TDB2> connect dwperf/dwperf@TDB
Connected.
you are utilising central dba scripts
altering the default date format to be dd-mon-yyyy hh24:mi
pause on, pages 24 head on recsep off
TDB> SELECT USER,sys_context('USERENV','SESSIONID'),SYS_CONTEXT('USERENV','SID') FROM DUAL;
any key>
USER SYS_CONTEXT('USERENV','SESSIONID') SYS_CONTEXT('USERENV','SID')
----------- ---------------------------------- ----------------------------
DWPERF 253785 524
1 row selected.
-- Again I was surprised to see the same SID. The AUDSID has increased of course.
-- I do wonder if someone created a session whilst I did this test or if a new AUDSID values is selected
-- from the sequence and discarded by the SYS logon. I should test that...
--
-- What will happen if I log of SQL*Plus completely and log back in
TDB>exit
-- and now I log back in...
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jun 24 14:23:46 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
you are utilising central dba scripts
altering the default date format to be dd-mon-yyyy hh24:mi
pause on, pages 24 head on recsep off
TDB> SELECT USER,sys_context('USERENV','SESSIONID'),SYS_CONTEXT('USERENV','SID') FROM DUAL;
any key>
USER
------------------------------
SYS_CONTEXT('USERENV','SESSIONID')
--------------------------------------------------------------------------------
SYS_CONTEXT('USERENV','SID')
--------------------------------------------------------------------------------
DWPERF
253786
524
1 row selected.
-- SAME ID!!!!
I must confess, I have never really looked into the persistance of SID before, I did think that Oracle would increment the SID with each and every logon and cycle around. However, it seems to me that the Oracle kernal keeps the constructs of a session around when that session logs out and re-uses it if another session is made quite soon afterwards. It makes sense, why destroy everything if you can re-use it soon after – it will speed up connections. As anyone who has had to deal with an oralce system with high session turnover knows, creating sessions is a very “heavy” task in Oracle. It seems lighter on 10 than 9 {and I am just going on “DBA Feel” when I say this, no concrete evidence} and I wonder if this re-use of SIDs is part of that. I should google it.
I am constantly guilty of starting a blog on one topic and drifting into another. So to keep up the pattern…
I had to edit my output to make the results of my check on USER, SID and AUDSID look OK in this posting. This is because, though your SID may be the same between SQL*Plus sessions, your column definitions ain’t. {If anyone thinks I should split this off to a dedicated BLOG post, just say so}
TDB> col user form a11
TDB> col SYS_CONTEXT('USERENV','SID') form a28
TDB> col SYS_CONTEXT('USERENV','SESSIONID') form a34
TDB> /
any key>
USER SYS_CONTEXT('USERENV','SESSIONID') SYS_CONTEXT('USERENV','SID')
----------- ---------------------------------- ----------------------------
DWPERF 253786 524
-- col commands seem not to be persisted in SQL*Plus when you log onto a new session
TDB> connect mdw/mdw@TDB
Connected.
you are utilising central dba scripts
altering the default date format to be dd-mon-yyyy hh24:mi
pause on, pages 24 head on recsep off
TDB> SELECT USER,sys_context('USERENV','SESSIONID'),SYS_CONTEXT('USERENV','SID') FROM DUAL;
any key>
USER
------------------------------
SYS_CONTEXT('USERENV','SESSIONID')
--------------------------------------------------------------------------------
SYS_CONTEXT('USERENV','SID')
--------------------------------------------------------------------------------
ODS
253795
524
1 row selected.
TDB>
-- You see?
-- An easier way to demo this is to check the defined variables and col settings with the
-- very useful, and often overlooked, naked DEFINE and COL commands
TDB> define
DEFINE _DATE = "24-JUN-2010 14:31" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "TDB" (CHAR)
DEFINE _USER = "MDW" (CHAR)
DEFINE _PRIVILEGE = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1002000100" (CHAR)
DEFINE _EDITOR = "Notepad" (CHAR)
DEFINE _O_VERSION = "Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options" (CHAR)
DEFINE _O_RELEASE = "1002000300" (CHAR)
DEFINE V_NEWPRO = "TDB" (CHAR)
TDB> col
SP2-0045: * no COLUMN defined
-- and just to show COL shows defined column definitions...
TDB> col eric form a30
TDB> col
COLUMN eric ON
FORMAT a30
TDB>
Martin Widlake's Yet Another Oracle Blog 
Hi,
Sid does not change, but serial does change.
Hi Joaquin,
That’s right, the serial# does change – if it did not then a kill command on sid,serial# could kill the wrong session! ie you get the sid and serial# of a session you want to terminate, check a couple of other things out, come back. Meanwhile that original session has logged out and someone else has logged in, getting the same SID (there is a limited number of SIDs used on any given system) – and you issue the ‘alter system kill session ‘sid,serial#’. Id the serial# was not altering, you would mistakenly kill the session 😦
Thanks,
Martin