Database Reference
In-Depth Information
Session Identification
Session identification is always emitted to SQL trace files. In contrast to module or action iden-
tification, it does not require any application coding. The format of the session identification is
identical in Oracle9
i
and Oracle10
g
, such that the Oracle10
g
TRCSESS utility can be used to
extract trace information for a single session from multiple Oracle9
i
or Oracle10
g
shared server
trace files.
Irrespective of the server model used (dedicated or shared) each session is uniquely iden-
tified by a combination of two figures during the lifetime of the instance. That is, the same
combination of figures will not be reused, unless the DBMS instance is shut down and restarted.
Contrast this with
V$SESSION.AUDSID
, which is derived from the sequence
SYS.AUDSES$
, used for
auditing purposes, and available by calling
USERENV('SESSIONID')
. Speaking of
USERENV
, Oracle10
g
and subsequent releases finally provide access to
V$SESSION.SID
to non-privileged users through
the undocumented option
SID
of the function
USERENV
as in
USERENV('SID')
.
5
Non-privileged in
this context means that access to V$ views has not been granted, for example through S
ELECT_
CATALOG_ROLE
.
As an aside, the most appropriate general way to figure out the
SID
in Oracle9
i
is to run
SELECT
sid
FROM
v$mystat
WHERE
ROWNUM=1
. Here, general means that this works for non-privileged sessions
as well as sessions with SYSDBA and SYSOPER privileges. The query
SELECT
sid
FROM
v$session
WHERE
audsid = userenv ('sessionid')
is inappropriate for getting the
SID
of privileged sessions,
since these are not assigned a unique auditing session identifier (
V$SESSION.AUDSID
). Privileged
sessions have
AUDSID=0
in Oracle9
i
and
AUDSID=4294967295
in Oracle10
g
. This fact is undocu-
mented in the
Database Reference
(Oracle9
i
and Oracle10
g
).
The first figure of the session identification is the session identifier, while the second is the
session serial number. The former is found in
V$SESSION.SID
, whereas the latter is accessible
through
V$SESSION.SERIAL#
and is incremented each time the
SID
is reused. The format of the
entry is shown here:
*** SESSION ID:(
sid.serial#
)
YYYY-MM-DD HH24:MI:SS.FF3
The timestamp at the end of the line is depicted using ORACLE date format models (see
Oracle Database SQL Reference 10g Release 2
, page 2-58).
FF3
represents three fractional
seconds. An actual entry written on February 6th, 2007 on behalf of a session with
V$SESSION.
SID=147
and
V$SESSION.SERIAL#=40
is shown here:
*** SESSION ID:(147.40) 2007-02-06 15:53:20.844
Service Name Identification
Service name identification is always emitted to Oracle10
g
and Oracle11
g
SQL trace files. Oracle9
i
does not have this feature. Service names in trace files refer to instance service names. Do not
confound these with Oracle Net service names defined in
tnsnames.ora
or a directory service
(see “Instance Service Name vs. Net Service Name” in this topic's Introduction for disambigu-
ation). The service name of a session established using the bequeath adapter—such sessions
are always running on the same machine as the DBMS instance, do not go through the listener,
and require setting
ORACLE_SID
to the same value the instance was started with—is “SYS$USERS”.
The documented alternative is
SELECT sys_context('USERENV', 'SID') FROM dual
.
5.