Oracle Database Query session and deadlock Solution

-- View the number of sessions selectcount (*) fromv $ session; -- view the number of processes selectcount (*) fromv $ process; -- view the number of concurrent connections of the database select * fromv $ sessionwherestatusACTIVE;

-- View the number of sessions select count (*) from v $ session; -- view the number of processes select count (*) from v $ process; -- view the number of concurrent connections of the database select * from v $ session where status = ACTIVE;--- view the session select sid, SERIAL #, USERNAME, PROGRAM, MACHINE, STATUS created by the current database

-- View the number of sessions
Select count (*) from v $ session;

-- View the number of processes
Select count (*) from v $ process;

-- View the number of concurrent connections of the database
Select * from v $ session where status = 'active ';-



-- View Sessions established by the current database
Select sid, SERIAL #, USERNAME, PROGRAM, MACHINE, status from v $ SESSION;

-- View the maximum number of connections allowed by the database
SELECT value from v $ parameter where name = 'processs'

-- View the maximum number of sessions allowed by the database
SELECT value from v $ parameter where name = 'session'


-- View SQL statements running in the background
Select. program, B. spid, c. SQL _text from v $ session a, v $ process B, v $ sqlarea c where. paddr = B. addr and. SQL _hashvalue = c. hash_value and. username is not null;
Iii. deadlock locating
By checking the database table, you can check which statement is deadlocked and which machine is causing the deadlock.
1) Use the dba user to execute the following statements
Select username, lockwait, status, machine, program from v $ session where sid in
(Select session_id from v $ locked_object)
If there are output results, it indicates a deadlock exists and you can see which machine is deadlocked. Field description:
Username: the database user used by the deadlock statement;
Lockwait: the status of the deadlock. If there is content, it indicates the deadlock.
Status: Status. active indicates a deadlock.
Machine: The Machine where the deadlock statement is located.
Program: the application that generates the deadlock statement.
2) You can run the following statements with dba to view the statements that have been deadlocked.
Select SQL _text from v $ SQL where hash_value in
(Select SQL _hash_value from v $ session where sid in
(Select session_id from v $ locked_object ))

Iv. Solution to deadlock
Generally, you only need to submit statements that generate deadlocks, but in the actual execution process. The user may not know which statement is used to generate a deadlock. You can close the program and restart it.

This problem is often encountered during the use of Oracle, so a few solutions are also summarized.

1) Find the deadlock process:

Sqlplus "/as sysdba" (sys/change_on_install)
SELECT s. username, l. OBJECT_ID, l. SESSION_ID, s. SERIAL #,
L. Oracle_USERNAME, l. OS _USER_NAME, l. PROCESS
From v $ LOCKED_OBJECT l, V $ session s where l. SESSION_ID = S. SID;

2) kill the deadlock process:

Alter system kill session 'sid, serial # '; (sid = l. session_id)

3) if the problem persists:

Select pro. spid from v $ session ses,
V $ process pro where ses. sid = XX and
Ses. paddr = pro. addr;

Replace the sid with the sid of the deadlock:

Exit
Ps-ef | grep spid

Spid is the process Number of the process and kill the Oracle process.