Shulou(Shulou.com)06/01 Report--

How to view the current session information of oracle

How do I check the current number of connections in Oracle? You just need to query it with the following SQL statement.

# View connections that are not currently empty

Select * from v$session where username is not null

# check the number of connections of different users

Select username,count (username) from v$session where username is not null group by username

# number of connections

Select count (*) from v$session

# concurrent connections

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

# maximum connection

Show parameter processes

# modify connection

Alter system set processes = value scope = spfile

# query Lock Table

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 LJV session se

Where a.object_id = l.object_id

And se.OSUSER = l.os_user_name

And l.oracle_username = 'mcczjzx'

# Kill a session

Alter system kill session 'SID,SERIAL#'

# v$session Field description

SADDR: session address

SID: session identifier, often used to join 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 query the sid of the current session through audsid.

Select sid from v$session where audsid=userenv ('sessionid')

PADDR: process address, associated with the addr field of the v$process, which can be used to look up the id of the process of the current session corresponding to the operating system.

USER#: session's user id . Equal to the user_id in dba_users. The user# of the Oracle internal process is 0.

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

COMMAND: the SQL Id that session is executing. 1 stands for create table,3 and represents select.

TADDR: the current transaction address. Can be used to associate the addr field of the v$transaction.

LOCKWAIT: you can use this field to query information about the lock you are currently waiting for. Sid & lockwait corresponds to sid & kaddr in v$lock.

STATUS: used to determine the status of the session. Active: executing SQL statement. Inactive: wait for the 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: client operating system user name.

PROCESS: client process id.

MACHINE: client machine name.

TERMINAL: the terminal name executed by the client.

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: the sql statement being executed by session 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: last executed sql statement.

MODULE, MODULE_HASH, ACTION, ACTION_HASH, CLIENT_INFO: apply some information set through DBMS_APPLICATION_INFO.

FIXED_TABLE_SEQUENCE: a number that increases when session completes a user call, that is, if session inactive, it does not increase. Therefore, the performance of session since a certain point in time can be monitored based on the change in the value of this field. For example, an hour ago, the FIXED_TABLE_SEQUENCE of a session was 10000, but now it is 20000, indicating that its user call is more frequent within an hour, so you can focus on the performance statistics of this session.

ROW_WAIT_OBJ#: is locked to the object_id of the table where the row is located. Associate with the object_id in dba_objects to get the locked table name.

The datafile id where the ROW_WAIT_FILE#: is locked. Associate with file# in v$datafile to get datafile name.

ROW_WAIT_BLOCK#: Identifier for the block containing the row specified in ROW_WAIT_ROW#

The locked row that ROW_WAIT_ROW#: session is currently waiting for.

LOGON_TIME: session logon time

Detailed explanation of v$process field

ADDR: process address . Can be associated with the paddr field of v$session.

PID: Oracle process identifier.

SPID: operating system process identifier.

USERNAME: the user name of the operating system process. Not an Oracle user name.

SERIAL#:: process serial number .

TERMINAL: operating system terminal identifier (e.g.computer name).

PROGRAM: the program that the process is executing (e.g.ORACLE.EXE (ARC0)), similar to program in v$session.

BACKGROUND: 1 stands for oracle background process,null and 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.spidgramm99999')) | | '.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'

With the spid known, view the statement that is currently being executed 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

Check locks and wait:

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.