Skip to Main Content
  • Questions

  • how can i use dbms_session.unique_session_id

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, rati.

Asked: November 28, 2016 - 6:33 am UTC

Last updated: November 28, 2016 - 5:59 pm UTC

Version: oracle 11g r2

Viewed 1000+ times

You Asked

dbms_session.unique_session_id returns unique value for my session. understood.

but the question is - what information can i gain from it? how or where i use the returned value?

are there any dba or system tables where i can: select * from table_about_sessions where session_id=returned value?

or is this just a way to define one session from another?

thank you. always gratefull for your help.

and Chris said...

DBMS_session also has a function is_session_alive, which takes this unique id as a parameter. So you can use it to check whether a session exists or not:

-- session 1
SQL> select dbms_session.unique_session_id from dual;

UNIQUE_SESSION_ID
-----------------------------------------------------

003316390001

-- session 2
begin
  if dbms_session.is_session_alive('003316390001') then 
    dbms_output.put_line('It lives!');
  else
    dbms_output.put_line('It''s dead Jim :(');
  end if;
end;
/

PL/SQL procedure successfully completed.
It lives!

-- session 1
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options

-- session 2
begin
  if dbms_session.is_session_alive('003316390001') then 
    dbms_output.put_line('It lives!');
  else
    dbms_output.put_line('It''s dead Jim :(');
  end if;
end;
/

PL/SQL procedure successfully completed.
It's dead Jim :(


You could also use it to store session specific information in a table to fetch later. But if the data is private to the session temporary tables (introduced waaaaaay back in 8i) will generally be a better solution.

Rating

  (2 ratings)

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

Comments

rati todua, November 28, 2016 - 11:57 am UTC

maa man. thanks for the answer and script.

"You could also use it to store session specific information..."

where or how can i find this session specific information?

Chris Saxon
November 28, 2016 - 5:58 pm UTC

It's whatever you want! I was just referring to data that is only relevant to your session. e.g. if you have a process that creates a data set you later use for further processing.

rati todua, November 28, 2016 - 12:50 pm UTC

i just executed select:

select to_number(substr(dbms_session.unique_session_id,1,4),'XXXXXXXXX' )from dual

and realized that
dbms_session.unique_session_id from
is same as sid.

should have guessed it by name, my bad...
it is clear where and how i use sid to query information about table.
thanks again
Chris Saxon
November 28, 2016 - 5:59 pm UTC

It's not the same as sid, but that is a part of it.
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.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.