V $ session and session information in Oracle

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, if the FIXED_TABLE_SEQUENCE of a session is 10000 an hour ago and the current value is 20000, it indicates that the user call is frequent within an hour. You can focus on the performance statistics of the 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

1 2 3 Next Page