sid serial# audsid V$SESSION

. SID: Session identifier, used to join to other columns
. SERIAL#: Counter, which is incremented each time a SID is reused by another session (when a session ends and another session starts and uses the same SID)
. AUDSID: Auditing session ID uniquely identifies a session over the life of a database. It is also useful when finding the parallel query slaves for a query coordinator (during the PQ execution they have the same AUDSID)

http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/sqlviews.htm

 

V$SESSION
This view has one row for every session connected to the database instance. The sessions include user sessions, as well as background processes like DBWR, LGWR, archiver.

See Also:
Oracle9i Database Concepts
 

Useful Columns for V$SESSION
V$SESSION is basically an information view used for finding the SID or SADDR of a user. However, it has some columns that change dynamically and are useful for examining a user. For example:

SQL_HASH_VALUE, SQL_ADDRESS: These identify the SQL statement currently being executed by the session. If NULL or 0, then the session is not executing any SQL statement. PREV_HASH_VALUE and PREV_ADDRESS identify the previous statement being executed by the session.

--------------------------------------------------------------------------------
Note:
When selecting from SQL*Plus, make sure that you have the column defined with adequate width (11 numbers wide) to see the complete number.

--------------------------------------------------------------------------------
 

STATUS: This column identifies if the session is:

Active: executing a SQL statement (waiting for/using a resource)
Inactive: waiting for more work (that is, SQL statements)
Killed: marked to be killed
The following columns provide information about the session and can be used to find a session when a combination (one or more) of the following are known:

Session Information
SID: Session identifier, used to join to other columns
SERIAL#: Counter, which is incremented each time a SID is reused by another session (when a session ends and another session starts and uses the same SID)
AUDSID: Auditing session ID uniquely identifies a session over the life of a database. It is also useful when finding the parallel query slaves for a query coordinator (during the PQ execution they have the same AUDSID)
USERNAME: The Oracle user name for the connected session
Client Information
The database session is initiated by a client process that could be running on the database server or connecting to the database across SQL*Net from a middle tier server or even a desktop. The following columns provide information about this client process:

OSUSER: Operating system user name for the client process
MACHINE: Machine where the client process is executing
TERMINAL: Terminal (if applicable) where the client process is running
PROCESS: Process ID of the client process
PROGRAM: Client program being executed by the client process
To display TERMINAL, OSUSER for users connecting from PCs, set the keys TERMINAL, USERNAME in ORACLE.INI or the Windows registry on their PCs if they are not showing up by default.

Application Information
Call the package DBMS_APPLICATION_INFO to set some information to identify the user. This shows up in the following columns:

CLIENT_INFO: Set in DBMS_APPLICATION_INFO
ACTION: Set in DBMS_APPLICATION_INFO
MODULE: Set in DBMS_APPLICATION_INFO
The following V$SESSION columns are also useful:

ROW_WAIT_OBJ#
ROW_WAIT_FILE#
ROW_WAIT_BLOCK#
ROW_WAIT_ROW#
Join Columns for V$SESSION
Table 24-16 is a list of several columns that can be used to join to other fixed views.

Table 24-16 Join Columns for V$SESSION
Column View Joined Column(s)
SID
 V$SESSION_WAIT, V$SESSTAT, V$LOCK, V$SESSION_EVENT, V$OPEN_CURSOR
 SID
 
(SQL_HASH_VALUE, SQL_ADDRESS)
 V$SQLTEXT, V$SQLAREA, V$SQL
 (HASH_VALUE, ADDRESS)
 
(PREV_HASH_VALUE, PREV_SQL_ADDRESS)
 V$SQLTEXT, V$SQLAREA, V$SQL
 (HASH_VALUE, ADDRESS)
 
TADDR
 V$TRANSACTION
 ADDR
 
PADDR
 V$PROCESS
 ADDR
 

Example 24-21 Finding Your Session
SELECT SID, OSUSER, USERNAME, MACHINE, PROCESS
  FROM V$SESSION
 WHERE audsid = userenv('SESSIONID');

  SID OSUSER     USERNAME    MACHINE     PROCESS
----- ---------- ----------- ----------- --------
  398 amerora    PERFSTAT    rgmdbs1     26582

Example 24-22 Finding a Session When the Machine Is Known
SELECT SID, OSUSER, USERNAME, MACHINE, TERMINAL
  FROM V$SESSION
 WHERE terminal = 'pts/tl'
   AND machine  = 'rgmdbs1';

 SID OSUSER    USERNAME  MACHINE    TERMINAL
---- --------- --------- ---------- ----------
 398 amerora   PERFSTAT  rgmdbs1    pts/tl

Example 24-23 Finding the SQL Statement Currently Being Run by a Session
It is a common requirement to find the SQL statement currently being executed by a given session. If a session is experiencing or responsible for a bottleneck, then the statement explains what the session might be doing.

col hash_value form. 99999999999
SELECT sql_hash_value hash_value
  FROM V$SESSION WHERE sid = 406;

HASH_VALUE
----------
4249174653
SQL> /

HASH_VALUE
----------
4249174653
SQL> /

HASH_VALUE
----------
4249174653
SQL> /

HASH_VALUE
----------
4249174653

This example waited for five seconds, executed the statement again, and repeated the action couple of times. The same hash_value comes up again and again, indicating that the statement is being executed by the session. As a next step, find the statement text using the view V$SQLTEXT and statement statistics from V$SQLAREA.

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8797129/viewspace-693031/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8797129/viewspace-693031/