V $ session and session information in Oracle

V $ session parameter SADDR: sessionaddressSID: sessionidentifier, which is often used to connect other columns. SERIAL #: sid will be reused, but when the same sid is reused

V $ session parameter SADDR: session addressSID: session identifier, which is often used to connect other columns. SERIAL #: sid will be reused, but when the same sid is reused

V $ session parameters

SADDR: session address
SID: session identifier, which is often used to connect other columns.
SERIAL #: sid will be reused, but when the same sid is reused, serial # will increase and will not be repeated.
AUDSID: audit session id. You can use audsid to query the sid of the current session. Select sid from v $ session where audsid = userenv ('sessionid ');
PADDR: process address, associated with the addr field of v $ process, you can use this field to investigate the id of the process corresponding to the current session operating system.
USER #: session's user id. It is equal to user_id in dba_users. The user # Of the Oracle internal process is 0. Associate all_usersch's user_id to query username
USERNAME: session's username. It is equal to the username in dba_users. The username of the Oracle internal process is empty. Associate all_usersch's user_id to query username
COMMAND: the Id of the SQL statement being executed by the session. 1 represents create table, 3 represents select.
TADDR: The current transaction address. It can be used to associate the addr field of v $ transaction.
LOCKWAIT: this field can be used to query information about the currently waiting lock. Sid & lockwait corresponds to sid & kaddr in v $ lock.
STATUS: used to determine the session STATUS. Active: the SQL statement is being executed. Inactive: Waiting for operation. Killed: marked as deleted.
SERVER: server type (dedicated or shared)
SCHEMA #: schema user id. The schema # Of the Oracle internal process is 0.
SCHEMANAME: schema username. The schemaname of the Oracle internal process is sys.
OSUSER: User Name of the client operating system.
PROCESS: client process id.
MACHINE: client machine name.
TERMINAL: the terminal name executed by the client.
PROGRAM: client application. For example, ORACLE. EXE (pmon1_ sqlplus.exe
TYPE: session type (background or user)
SQL _ADDRESS, SQL _HASH_VALUE, SQL _ID, SQL _CHILD_NUMBER: SQL statement being executed by the session, which corresponds to address, hash_value, SQL _id, and child_number in v $ SQL.
PREV_ SQL _ADDR, PREV_HASH_VALUE, PREV_ SQL _ID, PREV_CHILD_NUMBER: SQL statement executed last time.
MODULE, MODULE_HASH, ACTION, ACTION_HASH, and CLIENT_INFO: information set by the application through DBMS_APPLICATION_INFO.
FIXED_TABLE_SEQUENCE: a value that is added after the session completes a user call. That is to say, if the session is inactive, it will not increase. Therefore, you can monitor the performance of the session since a certain time point based on the value change of this field. For example, an hour ago, the FIXED_TABLE_SEQUENCE of a session was 10000, but now it is 20000, it indicates that the user call is frequent within an hour, you can focus on the performance statistics of this session.
ROW_WAIT_OBJ #: object_id of the table where the row is locked. And object_id in dba_objects to get the locked table name.
ROW_WAIT_FILE #: id of the datafile in which the row is locked. Associate with file # In v $ datafile to get the datafile name.
ROW_WAIT_BLOCK #: Identifier for the block containing the row specified in ROW_WAIT_ROW #
ROW_WAIT_ROW #: the row that the session is waiting.
LOGON_TIME: session logon time
ADDR: process address. It can be associated with the paddr field of v $ session.
PID: The Oracle process identifier.
SPID: The identifier of the operating system process.
USERNAME: the user name of the operating system process. It is not an Oracle user name.
SERIAL #: process serial number.
TERMINAL: the operating system terminal identifier (e.g., computer name ).
PROGRAM: the program being executed by the process (e.g., ORACLE. EXE (ARC0), similar to the PROGRAM in v $ session.
BACKGROUND: 1 represents oracle background process, and null represents normal process.
View the sid and serial of the current user #:
Select sid, serial #, status from v $ session where audsid = userenv ('sessionid ');
View the spid of the current user:
Select spid from v $ process p, v $ session s where s. audsid = userenv ('sessionid') and s. paddr = p. addr;
Select spid from v $ process p join v $ session s on p. addr = s. paddr and s. audsid = userenv ('sessionid ');
View the trace file path of the current user:
Select p. value | '/' | t. instance | '_ ora _' | ltrim (to_char (p. spid, 'fm99999') | '. trc'
From v $ process p, v $ session s, v $ parameter p, v $ thread t
Where p. addr = s. paddr and s. audsid = userenv ('sessionid') and p. name = 'user _ dump_dest ';
If spid is known, view the statement currently being executed or the last execution:
Select/* + ordered */SQL _text from v $ sqltext SQL
Where (SQL. hash_value, SQL. address) in (
Select decode (SQL _hash_value, 0, prev_hash_value, SQL _hash_value), decode (SQL _hash_value, 0, prev_ SQL _addr, SQL _address)
From v $ session s where s. paddr = (select addr from v $ process p where p. spid = to_number ('& pid ')))
Order by piece asc;
View the lock and wait:
Col user_name format a10
Col owner format a10
Col object_name format a15
Cola sid format 999999
Col serial # format 999999
Col spid format a6