How Oracle uses built-in scripting to check for jams

When diagnosing a problem with a lock, check with the holder, the person who is blocked. Often is very troublesome, if you do not accumulate the script, rely on Manual Check to check a lot of tables, such as V$lock,v$process p, v$session s, V$latchholder and so on. Now there's an easy way to use Oracle's built-in scripts to generate two views dba_waiters and dba_blockers to check for jams. Execute the built-in script to execute in sys, as follows:

C:documents and Settingsguogang>sqlplus/as sysdba;

Sql*plus:release 10.2.0.1.0-production on Friday January 24 08:26:42 2014
Copyright (c) 1982, +, Oracle. All rights reserved.
Connect to:
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.

The synonym has been created.

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

Experiment:

Simulate DML lock

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
--------------- --------------- ------------------------- ---------------------- ----------------------- ----------- --------- --------------- ---------
Transaction Exclusive Exclusive 131099 2550

Sql> select * from Dba_blockers;

Holding_session
---------------
150

It is clear to see that the holder of the lock is session 150, and the waiting person is 142.

Simulate DDL locks

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
--------------- --------------- -------------------------- ---------------------------------------- --------------- ------------------------- ---------- ----------
145 Transaction Exclusive Exclusive 1310729 1391

Sql> select * from Dba_blockers;

Holding_session
---------------
145

It is clear to see that the holder of the lock is session 145, and the waiting person is 150.