Home » RDBMS Server » Server Administration » unique session id
unique session id [message #244090] Mon, 11 June 2007 10:23 Go to next message
moschen
Messages: 25
Registered: April 2007
Junior Member
Hello,

I would like to log session acitivities. I would also like to be able to check if the session for which an activity has been logged is still active. The problem is, what do I need to uniquely identify a session?

DBMS_SESSION.UNIQUE_SESSION_ID - is explained as: unique for all sessions currently connected to this database. Meaning that if I restart the DB or connections are closed and opened, it might not be unique anymore, right?

SYS_CONTEXT('userenv','sessionid') - returns AUDSID, but is this garantied to be unique and not been reused, forever?

What I do not want to happen is, that I use a session identifier, which might reappear.

My best guess is currently:
SYS_CONTEXT('userenv','sessionid') + logon_time (from V$SESSION)

This combination should be really unique and should never reappear, right?

Best regards,
Andre
Re: unique session id [message #244102 is a reply to message #244090] Mon, 11 June 2007 10:46 Go to previous messageGo to next message
BlackSwan
Messages: 22839
Registered: January 2009
Senior Member
>The problem is, what do I need to uniquely identify a session?
Oracle uses SID & SERIAL# out of v$session to uniquely identify a session
Re: unique session id [message #244109 is a reply to message #244090] Mon, 11 June 2007 10:55 Go to previous messageGo to next message
Michel Cadot
Messages: 59280
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
that if I restart the DB or connections are closed and opened, it might not be unique anymore, right?

Yes, it is only unique inside the current instances of the database.

Quote:
SYS_CONTEXT('userenv','sessionid') - returns AUDSID, but is this garantied to be unique and not been reused, forever?

No.

Quote:
My best guess is currently:
SYS_CONTEXT('userenv','sessionid') + logon_time (from V$SESSION)
This combination should be really unique and should never reappear, right?

You have to add the thread id (v$thread.thread#) as in dbms_session.unique_session_id.

Regards
Michel

[Updated on: Mon, 11 June 2007 10:56]

Report message to a moderator

Re: unique session id [message #244293 is a reply to message #244109] Tue, 12 June 2007 04:53 Go to previous messageGo to next message
moschen
Messages: 25
Registered: April 2007
Junior Member
Hi Michel,

thanks for your reply!

Quote:
You have to add the thread id (v$thread.thread#) as in dbms_session.unique_session_id.


Could you elaborate on this? Why do I also need the thread#? Do you mean to identify the activity? Or can it happen that the AUDSID + logon_time is not enough to identify a session, meaning that it can happen that oracle can assign the same AUDSID if two sessions are created at the same moment.

My understanding was, that the AUDSID is unique at a certain time in time, so that if I do a

select * from v$session
where audsid = SYS_CONTEXT('userenv','sessionid')


it will always return at max 1 row. And to make it unique forever I could just add the logon_time.

How can I get the current thread#?

Best regards,
Andre
Re: unique session id [message #244301 is a reply to message #244293] Tue, 12 June 2007 05:19 Go to previous messageGo to next message
Michel Cadot
Messages: 59280
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You need thread# if you use RAC that is multiple instances for 1 database.
Each instance (thread# identifies the instance) can have the same (sid,logon_time) or (sid,serial#).
DBMS_SESSION.UNIQUE_SESSION_ID is sid+serial#+thread#.
If you want a unique id over the time, adding logon_time is a good idea.

Quote:
My understanding was, that the AUDSID is unique at a certain time in time

It is not true, even in non-RAC environment:
SQL> select count(*) from v$session
  2  where audsid = SYS_CONTEXT('userenv','sessionid')
  3  /
  COUNT(*)
----------
         2

1 row selected.

Regards
Michel


[Updated on: Fri, 15 June 2007 11:13]

Report message to a moderator

Re: unique session id [message #244368 is a reply to message #244301] Tue, 12 June 2007 08:33 Go to previous messageGo to next message
moschen
Messages: 25
Registered: April 2007
Junior Member
Ah, ok, for a single instance the AUDSID should be ok then. Any chance you could post the code to getting the thread#?

Thanks a lot for your support!

Best regards,
Andre
Re: unique session id [message #244377 is a reply to message #244368] Tue, 12 June 2007 09:05 Go to previous messageGo to next message
Michel Cadot
Messages: 59280
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select thread# from v$thread;

What else? Cool

Regards
Michel

[Updated on: Tue, 12 June 2007 09:06]

Report message to a moderator

Re: unique session id [message #245207 is a reply to message #244368] Fri, 15 June 2007 08:02 Go to previous messageGo to next message
aline
Messages: 92
Registered: February 2002
Member
moschen wrote on Tue, 12 June 2007 08:33
Ah, ok, for a single instance the AUDSID should be ok then.
Andre




hi
maybe not. all background process have 0 for their audsid.
you may have problem then if you audit all connect sessions and the job queue is not null!
Re: unique session id [message #245208 is a reply to message #245207] Fri, 15 June 2007 08:06 Go to previous messageGo to next message
Michel Cadot
Messages: 59280
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
See my example above: http://www.orafaq.com/forum/mv/msg/83356/244301/102589/#msg_244301
Didn't I show that several sessions can have the same audsid?

Regards
Michel
icon7.gif  Re: unique session id [message #245224 is a reply to message #245208] Fri, 15 June 2007 09:58 Go to previous messageGo to next message
aline
Messages: 92
Registered: February 2002
Member
of course, i saw it Razz !
I gave just another example for having many process with same audsid.
U spoke about the problem of a database with many thread (i.e a RAC) and possibly two process with same audsid and I just confirm with another example (i.e jobs with of course same audsid)
Re: unique session id [message #245238 is a reply to message #245224] Fri, 15 June 2007 11:11 Go to previous messageGo to next message
Michel Cadot
Messages: 59280
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I was answering about one the OP sentence about AUDSID and I queried v$session that is 1 thread.
To view all threads, you need to query Gv$session.

(I now changed my answer to make it clearer.)

Regards
Michel
Re: unique session id [message #592550 is a reply to message #245238] Fri, 09 August 2013 09:42 Go to previous message
suhong
Messages: 8
Registered: May 2007
Location: NJ/NY
Junior Member
We have a RAC database (node:2) and our solution is to use combination of instance id and audsid from:
SYS_CONTEXT ('USERENV', 'INSTANCE')
SYS_CONTEXT ('USERENV', 'SESSION_USERID') from DUAL;

this will keep tracking session in unique.

Hope this helps.

- Hank
Previous Topic: my issue regarding a tablespace
Next Topic: DBVerify issue
Goto Forum:
  


Current Time: Wed Oct 01 01:24:14 CDT 2014

Total time taken to generate the page: 0.16955 seconds