jump to navigation

SID stays same, AUDSID alters June 24, 2010

Posted by mwidlake in internals.
Tags: ,
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>
About these ads

Comments»

1. Joaquin Gonzalez - July 23, 2010

Hi,

Sid does not change, but serial does change.

mwidlake - July 23, 2010

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


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 158 other followers

%d bloggers like this: