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.
 
Search WWH ::




Custom Search