1.The
v$locked_object
View
The columns of this view are:
•
XIDUSN
: Rollback segment number
•
OBJECT_ID
: ID of the object being modified
•
SESSION_ID
: ID of the session locking the object
•
ORACLE_USERNAME
•
LOCKED_MODE
Example
To find the table name that corresponds to a particular object ID in the
v$locked_object
view:
SQL> SELECT xidusn, object_id, session_id, locked_mode
2 FROM v$locked_object;
XIDUSN OBJECT_ID SESSION_ID LOCKED_MODE
--------- --------- ---------- -----------
3 2711 9 3
0 2711 7 3
3 2711 9 3
0 2711 7 3
SQL> SELECT object_name FROM dba_objects
2 WHERE object_id = 2711;
OBJECT_NAME
-------------
EMPLOYEES
-------------
EMPLOYEES
If the value of
xidusn
is 0, then the session with the corresponding session ID is requesting and
waiting for the lock being held by the session, for which
xidusn
value is different from 0.
2.The
utllockt.sql
Script
You can also use the
utllockt.sql
script to display lock wait-for in a hierarchy. The script prints
the sessions that are waiting for locks and the sessions that are blocking.
You must run the
catblock.sql
script (found in
$ORACLE_HOME/rdbms/admin
folder) as a
sysdba
user before using
utllockt.sql
. The
catblock.sql
script creates the
dba_locks
and
dba_blockers
views along with others that will be used by
utllockt.sql
.
For example, in the following output session 9 is waiting for session 8, sessions 7 and 10 are waiting for
9.
WAITING TYPE MODE MODE LOCK LOCK
SESSION REQUESTED HELD ID1 ID2
SESSION REQUESTED HELD ID1 ID2
------- ---- ------------- ------------- ----- -----
8 NONE None None 0 0
9 TX Share (S) Exclusive (X) 65547 16
8 NONE None None 0 0
9 TX Share (S) Exclusive (X) 65547 16
7 RW Exclusive (X) S/Row-X (SSX) 33554440 2
10 RW Exclusive (X) S/Row-X (SSX) 33554440 2
3.The
v$lock
View
Two of the columns in this view are
type
and
id1
. These columns have the values:
Lock typeID1
TXRollback segment number and slot number
TMObject ID of the table being modified
Any process that is blocking others is likely to be holding a lock obtained by a user application. The
locks acquired by user applications are:
•
Table locks (TM)
•
Row-level locks (TX)
To find the table name that corresponds to a particular resource ID 1 of the
v$lock
view:
SQL> SELECT object_name
2 FROM dba_objects, v$lock
3 WHERE object_id=id1 AND type='TM';