Tom

Thanks for all your valuable insights. I have a follow-up question. After reading many of your responses on SESSIONID, is it correct to state that

- SID combined (somehow) with SERIAL# make up SESSIONID
- SESSIONID = AUDSID (just different name when use for auditing)
- SID and SERIAL# are not unique each can be reused there SESSIONID is also not UNIQUE i.e. two users at a different time can have the same SESSIONID. It is not unique across time in the same database.
- Is there a routine to get a (1) within the database ID that is always UNIQUE (2) across the database, a globally unique number.

Thanks

Tom,

you said

sessionid is unique but can wrap around over time.

But what I see is sid is getting generated on a max+1 basis . while sessionid seems sequence generated unique ( I am not sure if it wraps around over time).

This is my session output

pk@fd>@connect pk@ed
Enter password: ***
Connected.
pk@ed>select distinct sid from v$mystat;

SID
----------
12


pk@ed>select sid,audsid from v$session;

SID AUDSID
---------- ----------
1 0
2 0
3 0
4 0
5 0
6 0
10 10892773
12 10892774

8 rows selected.

pk@ed>disco
Disconnected from Oracle9i Enterprise Edition Release 9.0.1.3.0 - 64bit Production
With the Partitioning option
JServer Release 9.0.1.3.0 - Production
pk@ed>@connect pk@ed
Enter password: ***
Connected.
pk@ed>select distinct sid from v$mystat;

SID
----------
12

pk@ed>select sid,audsid from v$session;

SID AUDSID
---------- ----------
1 0
2 0
3 0
4 0
5 0
6 0
10 10892773
12(same as session before) 10892775(Changed)

8 rows selected.

your input please.

Thanks

I am using the sessionid as a UK as part of my application auditing. So - now that I read your comment - I am concerned about wrapping / duplicates. In the last 12 months - the sessionid in our audit table has gone from about 4,000,000 to about 12,100,000.
The SYS.AUDSES$ (which you mention is where the sessionid comes from) has a max value of 2,000,000,000 so I guess we will not "wrap" (during my tenure) - assuming the last 12 months is somewhat typical of future activity.
However - I am wondering what other ways the sequence could become duplicated. For instance - we are currently on Oracle 9.2.0.4 database server. We are supposed to be moving on to Oracle 10.? sometime next year.
Depending on how the "move" is accomplished - is it reasonable to be concerned about the sessionid on the database being "reset" and not "moving over" at the same currval?

I find the sessionid to be a very convenient UK. I have FK pointing to it - works nice. But I am wondering - if this will be a problem I would (of course) rather deal with it ahead of time rather than having to scurry at the last minute. (Plus I like fixing things before anybody else knows I broke it!!!) Is there a "database id" which would be unique for the database instance that I could store as another column and then make the UK the combination of the sessionid and the databaseid?
Or would you not see that as a good idea or perhaps have a better suggestion?

Tom,

I have three sessions (all SYS user) that have the same
audsid. Doesn't uniqueness of audsid apply to SYS user sessions?
1* select sid,serial#,audsid,status from v$session where username is not null and terminal is no
SYS@dev:SQL> /

SID SERIAL# AUDSID STATUS
---------- ---------- ---------- --------
119 15763 320250 INACTIVE
120 23406 320262 INACTIVE
121 1164 318277 INACTIVE
124 64202 320174 INACTIVE
126 11401 316806 INACTIVE
127 22526 4294967295 ACTIVE
130 23565 316930 INACTIVE
131 28189 320045 INACTIVE
132 21807 316923 INACTIVE
133 23376 320146 INACTIVE
134 17579 4294967295 INACTIVE
136 47362 320046 INACTIVE
139 32268 4294967295 KILLED
141 12878 316807 ACTIVE
158 10289 314464 INACTIVE

15 rows selected.

Hello Tom,
Why do I see lot of INACTIVE sessions in V$SESSIONS ?

The information is correct but examples use userenv('sessionid');

This usage is depreciated and should be replaced with SYS_CONTEXT('USERENV','sessionid');