Re: Q: Session IDs

Wolf Pfannenstiel <wolfp_at_cs.tu-berlin.de> wrote in message news:<9gq8n1$q8q$1_at_news.cs.tu-berlin.de>...
> Hi everyone,
>
> we have a question regarding session ids. The simple
> version of the question is:
>
> How do I get the session id plus serial# in an (interactive or
> shell-script driven) sqlplus session?
>
> We need a unambiguous identifier for the current session. The session
> id alone is not sufficient for this purpose, we also need the
> serial#. However, where can we find this information? The table
> v$session contains data for all sessions. The table v$mystat seems to
> be the only table that contains information specific to the current
> session. However, it contains only the session id, not the serial#. Is
> the assumption safe, that, at any one time, there is only one entry
> with the same sid value in v$session?
>
> (We cannot use the user name as specifier because there may be more
> than one session active from this user.)
>
>
> Here is the long version of the question:
>
> Ultimately, we would like to be able to check from the outside
> (i.e. from another session) whether a session is still active or not.
> The idea is: the session in question retrieves its session id plus
> serial# and writes it into a table when it starts. It removes the
> entry when it finishes. Some time later, another session checks the
> state of the first session. If the entry is still there, the first
> session is apparently still running. However, if the database (or
> server machine) has crashed for some reason, the session may have been
> interrupted, so it is not running anymore, but the table entry is
> still there. We would like to detect this condition by checking
> whether the first session is still running, so we look for the first
> session's session id and serial# in the v$session table. Of course, to
> be able to do so, the first session must know its own id to store it
> in the table when it starts.
>
> Any help is greatly appreciated,
> thanks,
> Wolf

DDC2> l
  1 select sid, serial#, username
  2 from v$session
  3 where sid = (select sid

  4               from   v$mystat
  5               where  rownum = 1
  6*             )

---------- ---------- ------------------------------
        40       7138 MPOWEL01

Prior to v$mystat you used the userenv function to get the session id which you then joined to v$session.audsid.

• Mark D Powell --