| unique session id [message #244090] |
Mon, 11 June 2007 10:23  |
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 #244109 is a reply to message #244090] |
Mon, 11 June 2007 10:55  |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
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 |
|
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
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 #245207 is a reply to message #244368] |
Fri, 15 June 2007 08:02 |
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 #592550 is a reply to message #245238] |
Fri, 09 August 2013 09:42 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
!