Ways to find blocked and blocked SIDs in Oracle ____oracle

In Oracle, blocking and blocking are frequently encountered.

There are several main ways to find blocking and blocking:
I. By looking for V$lock and V$locked_object
This is the most common and direct method.

Sql> Select Sid,block from V$lock where block=1;

SID Block
---------- ----------
252 1

--as you can see, sid=252 's session blocks the rest of the session.

Sql>
Sql> Select object_id,session_id from V$locked_object where object_id into (select object_id from V$locked_object where s ession_id=252);

OBJECT_ID session_id
---------- ----------
63833 252
63833 269

Sql>

--it can be concluded that sid=252 's session blocked sid=269 's session.

Two. By looking for Dba_waiters and dba_blockers
Sql> select * from Dba_blockers;

Holding_session
---------------
252

As you can see, sid=252 's session blocks other sessions.

Sql> select Waiting_session,holding_session from Dba_waiters;

Waiting_session holding_session
--------------- ---------------
269 252

As you can see, the holding_session is 252 and the waiting waiting_session is 269.

Three. In Oracle 10G, you can find through the Blocking_session field in V$session
Sql> Select Sid,blocking_session from v$session where blocking_session are NOT null;

SID blocking_session
---------- ----------------
269 252

Original address: http://charsi.itpub.net/post/42352/516427