How to use a built-in script to query congestion in Oracle

When diagnosing the lock issue, you need to find the owner and the blocked person. It is often very troublesome. If you have no accumulated scripts, You need to manually query many tables, such as v $ lock, v $ process p, v $ session s, and v $ latchholder. Now there is a simple way to use Oracle's built-in scripts to generate two views dba_waiters and dba_blockers to check for blocking. Execute the built-in script in SYS as follows:

C: \ Documents ents and Settings \ guogang> sqlplus/as sysdba;

SQL * Plus: Release 10.2.0.1.0-Production on Friday January 24 08:26:42 2014
Copyright (c) 1982,200 5, Oracle. All rights reserved.
Connect:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the Partitioning, OLAP and Data Mining options
SQL> @? /Rdbms/admin/catblock. SQL
The view has been created.

You have created a synonym.

.........................

Lab:

Simulate DML locks

Session1:

Select * from test for update;

Session2:

Select * from test for update;

Session3:

SQL> select * from dba_waiters;

WAITING_SESSION HOLDING_SESSION LOCK_TYPE MODE_HELD MODE_REQUESTED LOCK_ID1 LOCK_ID2
------------------------------------------------------------------------------------------------------------------------------------------------
142 150 Transaction Exclusive 131099 2550

SQL> select * from dba_blockers;

HOLDING_SESSION
---------------
150

We can clearly see that the lock holder is session 150, and the waiting person is 142.

Simulate DDL lock

Session1:

Select * from obj $ for update

Session2:

Create index ind_t_id on test (object_id );

Session3:

SQL> select * from dba_waiters;

WAITING_SESSION HOLDING_SESSION LOCK_TYPE MODE_HELD MODE_REQUESTED LOCK_ID1 LOCK_ID2
------------------------------------------------------------------------------------------------------------------------------------------------------------
150 145 Transaction Exclusive 1310729 1391

SQL> select * from dba_blockers;

HOLDING_SESSION
---------------
145

We can clearly see that the lock holder is session 145, and the waiting person is 150.