Skip to Main Content
  • Questions

  • userenv('sessionid') -- are the values reused?

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, anthony .

Asked: May 02, 2000 - 1:20 pm UTC

Last updated: September 15, 2007 - 3:51 pm UTC

Version:

Viewed 10K+ times! This question is

You Asked

I use the following query to get session id.
Select userenv('SESSIONID') From Dual.

I have two doubts about using the above query.
(1). Let us assume that Session Id 1188 is created for one session.
After some time this session is over( logged out)and another user
gets connected to database. Can the session id 1188 be assigned
to the new session?.Is there any possibilities for that?

(2). Can the session id 1188 get repeated if the database is
shutdown and restarted Or the session id is uniqe throughout the
life of the database?



Please clear my doubts.



and Tom said...



Yes. sessionid is really the audses$ sequence. It is set to cycle. Its max_value may vary by implementation but eventually it can cycle. Once upon a time (7.3) there was a bug in Context and the fix was to make the max_value of audses$ 32,765. In that case, it would cycle frequently.

sys@8i> select * from user_sequences where sequence_name = 'AUDSES$';

SEQUENCE_N MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
---------- ---------- ---------- ------------ - - ---------- -----------
AUDSES$ 1 2000000000 1 Y N 20 10535706

sys@8i> select userenv('sessionid') from dual;

USERENV('SESSIONID')
--------------------
10535689


After 2,000,000,000 (2 billion) logins -- the numbers will be reused. If you export/import the whole database -- the sequence will be reused. If you use a database where the max_value is set smaller, it'll be reused faster. You might consider using your own 28 digit sequence with its own characteristics instead of relying on sessionid if uniqueness and portability across database instances is important to you.



Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

What if session with the new id already exist?

A reader, July 19, 2004 - 3:45 pm UTC

consider [ for the time being ] the max value in my sequence is just 500. i started a session with id 1. 499 new session started and terminated. my session with id 1 still exist.

now while assigning id to new session does oracle check that id with nextval of sequence exist or not?

Tom Kyte
July 19, 2004 - 4:47 pm UTC

given that the sequence would not cycle that quickly -- i've never tried it. you'd have to try and see but it would take a really long time since

o the sequence doesn't cycle that rapidly
o altering it is forbidden unless support tells you to.

More than one record on v$session for sessionid

A reader, September 11, 2007 - 3:57 pm UTC

Hi Tom please, on 10.2.0.3 I got on a database the problem I get three records for the same sessionid on v$session on a trigger, I searched and I couldn't find the reason (it's not sysoper, sydba, etc. it isn't 0 ).
Do you know why I don't get only one record?.

Thank you :).

SELECT LOGON_TIME, OSUSER, MACHINE, PROGRAM
INTO cHI_FECHALOGON, cHI_USUARIOSIS , cHI_MAQUINA, cHI_PROGRAMA
FROM V$SESSION WHERE AUDSID = USERENV( 'SESSIONID' );

returns 3 records.

Tom Kyte
September 15, 2007 - 3:51 pm UTC

well, my first thought would have been "let me look at the three records and supply that information too - for they might have some HUGE CLUE in them as to what is happening"


no, no idea for you. I would suggest using

where sid = (select sid from v$mystat where rownum=1)

instead of the somewhat un-reliable audsid....

to A reader

Michel Cadot, September 16, 2007 - 2:42 am UTC


You can also use sys_context and userenv/sid:
SQL> select sid, to_number(sys_context('userenv','sid')) sid from v$mystat where rownum=1;
       SID        SID
---------- ----------
       147        147

1 row selected.

Regards
Michel

Thank you Tom

A reader, September 18, 2007 - 5:25 pm UTC


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.