How to view the current session information for Oracle

How to view the current session information for Oracle

How do I see the current number of connections to Oracle? Just use the following SQL statement to query it.

#查看当前不为空的连接
SELECT * FROM v$session where username are not null

#查看不同用户的连接数

Select Username,count (username) from v$session where username isn't null GROUP by username

#连接数

Select COUNT (*) from v$session

#并发连接数

Select Count (*) from v$session where status= ' ACTIVE '

#最大连接

Show parameter processes

#修改连接

alter system SET processes = value scope = SPFile

#查询锁表
Select A.owner,
A.object_name,
A.subobject_name,
A.object_type,
L.SESSION_ID,
L.oracle_username,
L.os_user_name,
Se. Sid
Se. serial#
From All_objects A, v$locked_object l,v$session se
where a.object_id = l.object_id
and SE. Osuser = L.os_user_name
and l.oracle_username = ' mcczjzx ';

#杀掉某session
Alter system kill session ' sid,serial# '

#v $session Field Description

Saddr:session Address

Sid:session identifier, commonly used to connect other columns.

serial#: The SID is reused, but when the same SID is reused, the serial# is incremented and not duplicated.

Audsid:audit session ID. The SID of the current session can be queried by AUDSID.

Select Sid from V$session where Audsid=userenv (' SessionID ');

Paddr:process address, the addr field of the associated v$process, which can be used to investigate the ID of the process that currently corresponds to the operating system of the session.

user#: Session ' s user ID. equals the user_id in Dba_users. The user# of Oracle internal processes is 0.

Username:session ' s USERNAME. equals the username in dba_users. The username of the Oracle internal process is empty.

Command:session the SQL Id being executed. 1 represents create table,3 for select.

TADDR: The current transaction address. The addr field that can be used to correlate v$transaction.

Lockwait: This field allows you to query for information about the lock you are currently waiting for. Sid & Sid & in Lockwait and V$lock KADDR corresponds.

Status: Used to determine the session state. Active: Executing the SQL statement. Inactive: Wait for operation. Killed: is marked as deleted.

Server:server type (dedicated or shared)

schema#: Schema User ID. The schema# of Oracle internal processes is 0.

Schemaname:schema username. The SchemaName of the Oracle internal process is sys.

Osuser: Client operating system user name.

Process: Client process ID.

Machine: Client machine name.

TERMINAL: The client executes the TERMINAL name.

Program: Client application. such as ORACLE.EXE (Pmon) or Sqlplus.exe

Type:session type (background or user)

Sql_address, Sql_hash_value, sql_id, sql_child_number:session are executing SQL statement, and V$sql in ADDRESS, Hash_value, sql_id, Child_number corresponds.

Prev_sql_addr, Prev_hash_value, prev_sql_id, Prev_child_number: The last SQL statement executed.

MODULE, Module_hash, ACTION, Action_hash, Client_info: Apply some of the information that is set by the Dbms_application_info.

Fixed_table_sequence: A value added when the session completes a user call, that is, if the session inactive, it does not increase. You can therefore monitor the performance of the session since a certain point in time based on the change in the value of this field. For example, one hours ago, a session with a fixed_table_sequence of 10000, and now 20000, indicates that the user call is more frequent within one hours and can focus on the performance of the session Statistics

row_wait_obj#: The object_id of the table to which the row is locked. The OBJECT_ID Association in Dba_objects can get the table name that is locked.

row_wait_file#: The datafile ID where the row is locked. The file# Association in V$datafile can get datafile name.

row_wait_block#: Identifier for the BLOCK containing the row specified in row_wait_row#

row_wait_row#: The locked line that the session is currently waiting for.

Logon_time:session logon time

v$process Field Explanation

Addr:process address. Can be associated with the V$session paddr field.

Pid:oracle process identifier.

SPID: Operating system process identifier.

USERNAME: The user name of the operating system process. is not an Oracle user name.

serial#:: Process SERIAL number.

TERMINAL: Operating system TERMINAL identifier (e.g., computer name).

Program: The process is executing programs (e.g., ORACLE. EXE (ARC0)), similar to the program in V$session.

Background:1 represents the normal process for Oracle BACKGROUND Process,null.

To view the current user's SID and serial#:
Select Sid, Serial#, status from V$session where Audsid=userenv (' SessionID ');

To view the current user's SPID:
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 joins V$session s on P.addr=s.paddr and s.audsid=userenv (' SessionID ');

To view the trace file path for 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 ';

Known SPID, view the statement that is currently executing or last executed:
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;

To view locks and waits:
Col user_name format A10
Col owner Format A10
Col object_name format A15
Col SID format 999999
Col serial# format 999999
Col spid format A6
Select/*+ Rule */Lpad (", Decode (L.XIDUSN, 0, 3, 0)) | | L.oracle_username user_name,
O.owner, O.object_name, O.object_type, S.sid, s.serial#, P.spid
From V$locked_object L, dba_objects O, v$session S, v$process p
where l.object_id = o.object_id and l.session_id = s.sid and s.paddr = p.addr
Order by o.object_id, Xidusn desc;

How to view the current session information for Oracle