Q: Session IDs

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 Received on Wed Jun 20 2001 - 08:33:21 CDT