View the two script shares that cause Session blocking in Oracle, oraclesession

View the two script shares that cause Session blocking in Oracle, oraclesession

User A deletes the file but does not submit the file.

Copy codeThe Code is as follows:
SQL> delete from test where object_id <10;

Eight rows have been deleted.

If user B deletes or updates a record whose id is <10, it is blocked.

Copy codeThe Code is as follows:
SQL> update test set flag = 'n' where object_id <10;

In this case, you must first determine the problem. You can use the following script.

Copy codeThe Code is as follows:
Select t2.username, t2.sid, t2.serial #, t2.logon _ time
From v $ locked_object t1, v $ session t2
Where t1.session _ id = t2.sid order by t2.logon _ time;

The result is as follows:

Copy codeThe Code is as follows:
Username sid serial # LOGON_TIME
----------------------------------------------------------------
LIHUILIN 14 87 months-13
LIHUILIN 139 655-13

Or use

Copy codeThe Code is as follows:
Select
(Select username from v $ session where sid = a. sid) blocker,
A. sid, 'is blocking ',
(Select username from v $ session where sid = B. sid) blockee,
B. sid
From v $ lock a, v $ lock B
Where a. block = 1 and B. request> 0 and a. id1 = B. id1 and a. id2 = B. id2;

The result is as follows:

Copy codeThe Code is as follows:
Blocker sid 'isblocking' BLOCKEE SID
---------------------------------------------------------------------------------------------
LIHUILIN 14 is blocking LIHUILIN 139

Kill the blocked Session

Copy codeThe Code is as follows:
Select 'alter system kill session ''' | sid | ',' | serial # | '''; 'COMMAND from v $ session where username = 'lihuilin' and sid = 14;

The result is as follows:

Copy codeThe Code is as follows:
CMD
-----------------------------------------
Alter system kill session '14, 87 ';

Finally, execute the alter system command to remove blocking.

How can I check whether the system is blocked due to an abnormal session in the oracle database?

In this case, you need to find the session that causes the exception and block it and clear it. Oracle session has three features: (1) one session may block multiple sessions; (2) One session may be blocked by one session at most; (3) the session blocking relationship will not form a loop. (The loop is a deadlock, and oracle can automatically remove it) So the blocking relationship of sessions is a tree, and the BLOCK blocking relationship of all sessions in the DB system is a forest composed of several session blocking relationship trees, the abnormal session will surely become the root in the case of a failure ). Therefore, the process of searching for an abnormal lock table session is to find the abnormal root. It is generally considered that the abnormal root has two features: (1) the block tree is too large, and the blocking tree is the total number of sessions blocked by the root layer; (2) the average blocking wait time is too long. Method 1: OEM-> performance-> Blocking Sessions: select r. root_sid, s. serial #, r. blocked_num, r. avg_wait_seconds, s. username, s. status, s. event, s. MACHINE, s. PROGRAM, s. SQL _id, s. prev_ SQL _idfrom (select root_sid, avg (latency) as avg_wait_seconds, count (*)-1 as blocked_numfrom (select CONNECT_BY_ROOT sid as root_sid, seconds_in_waitfrom v $ sessionstart with blocking_session is nu Llconnect by prior sid = blocking_session) group by root_sidhaving count (*)> 1) r, v $ session swhere r. root_sid = s. sidorder by r. blocked_num desc, r. avg_wait_seconds desc; this SQL statement is used to count and sort and average blocking time of blocked sessions by blocking_session in the v $ session field. The most advanced SQL statement is abnormal sessions.

How can I check whether the system is blocked due to an abnormal session in the oracle database?

Sometimes an exception occurs during Oracle Database O & M. due to incorrect operations or code bugs, the session holds the lock abnormally and does not release the lock, and many system conversations are blocked. In this case, you need to find the session that causes the exception and block it and clear it.
Oracle session has three features:
(1) One session may block multiple sessions;
(2) A session can be blocked by at most one session;
(3) The session blocking relationship does not form a loop. (Loop is a deadlock, and oracle can be automatically released)
Therefore, the session blocking relationship is a tree, and the BLOCK blocking relationship of all sessions in the DB system is a forest composed of several session blocking relationship trees, the abnormal session will surely become the root in the case of a failure ). Therefore, the process of searching for an abnormal lock table session is to find the abnormal root.
It is generally considered that the abnormal root has two features: (1) the block tree is too large, and the blocking tree is the total number of sessions blocked by the root layer; (2) the average blocking wait time is too long.
Method 1:
OEM-> performance-> Blocking Sessions
Method 2:
Select r. root_sid, s. serial #,
R. blocked_num, r. avg_wait_seconds,
S. username, s. status, s. event, s. MACHINE,
S. PROGRAM, s. SQL _id, s. prev_ SQL _id
From (select root_sid, avg (seconds_in_wait) as avg_wait_seconds,
Count (*)-1 as blocked_num
From (select CONNECT_BY_ROOT sid as root_sid, seconds_in_wait
From v $ session
Start with blocking_session is null
Connect by prior sid = blocking_session)
Group by root_sid
Having count (*)> 1) r,
V $ session s
Where r. root_sid = s. sid
Order by r. blocked_num desc, r. avg_wait_seconds desc;
This SQL statement counts the number of sessions blocked by the blocked tree root according to the blocking_session field of v $ session, and sorts the average blocking time. The most advanced SQL statement is the abnormal session.
In addition, sessions with the longest lock hold time or the longest wait time are not necessarily the root cause of blocking!