Listing 5.3 shows the output of this script.

Listing 5.3 The output of the locking script.

Database     Lock         Mode  Mode          Session             Active
Object       Type         Held  Requested     ID          Username Image
------------------------------------------------------------------
102 BANK     OPS$MCACIAT  @xdc  (Pipe Two-Task DML Row-SELECT (SS)  None
57 INVOICE   OPS$DMAHTROB @xdc  (Pipe Two-Task DML Row-X      (SX)  None
57 LINE_ITEM OPS$DMAHTROB @xdc  (Pipe Two-Task DML Row-X      (SX)  None
70 LINE_ITEM OPS$JCONVICK @xdc  (Pipe Two-Task DML Row-X      (SX)  None
29 LINE_ITEM OPS$NUMNUTS  @xdc  (Pipe Two-Task DML Row-X      (SX)  None
70 LINE_ITEM OPS$JKONV    @xdc  (Pipe Two-Task DML Row-X      (SX)  None
57 LINE_ITEM OPS$DMAHTROB @xdc  (Pipe Two-Task DML Row-X

You can also use Oracle Monitor within SQL*DBA to look at locks. Listings 5.4 and 5.5 show some sample screens.

Listing 5.4 SQL*DBA—monitor session.

Session  Serial  Process                      Lock
  ID     Number    ID    Status   Username    Waited   Current Statement
========================================================================
  6      35       28     ACTIVE   BURLESON   C4D2B7A4     UPDATE
  8      70       19    INACTIVE   TYTLER                 SELECT
 12      15       25    INACTIVE   JONES                  INSERT
 14      17       27     ACTIVE    PAPAJ     C3D2B638     DELETE
 15      30       26     ACTIVE    ODELL                  UNKNOWN

In Listing 5.4, the Lock Waited column is the one that tells us the address of the lock that is being waited for. In this example, we can see that Burleson and Gaston are waiting on a lock held by Tytler.

Listing 5.5 SQL*DBA—monitor lock.

         Session Serial  Lock    Res      Res     Mode    Mode
Username    ID   Number  Type    ID 1     ID 2    Held    Requested
===================================================================
BURLESON    5      23     TM      23294    0       RX      NONE
BURLESON    5      23     TM      22295    0       RX      NONE
BURLESON    5      23     TX     266654    87     NONE      X
BURLESON    5      23     TX    3276482    97      X       NONE
TYTLER     14      13     TM       2211    0       RX      NONE
TYTLER     14      13     TM       2223    0       RX      NONE
TYTLER     14      13     TX     266654    87      X       NONE
GASTON     19      47     TM       2334    0       RX      NONE
GASTON     19      47     TM       2233    0       R
GASTON     19      47     TX     266654    87     NONE      X
GASTON     19      47     TX     193446    87      X       NONE

Under the “type” column are four types of locks:

•  TX (Transaction)—Decimal representation of rollback segment number “wrap” number (number and slot number times the rollback slot has been reused).

•  TM (Table Locks)—Object ID of table being modified (always 0).

•  RW (Row Wait)—Decimal representation of file number and decimal representation of row within block.

•  UL (User Defined Locks)—Complete list is found in Chapter 10 of the Oracle7 Concepts Manual or in Appendix B-81 in the Oracle7 Admin Guide.

The users with an “X” in the Mode Requested column are waiting for a lock release. The following users are waiting for lock 266654:

BURLESON               5     23   TX      266654       87
NONE      X
GASTON                19     47   TX      266654       87
NONE      X

We can look for this lock ID, and see that the following resource is holding the lock:

TYTLER                14     13   TX      266654       87
X   NONE

Often, the user(s) may be modifying many tables within the same transaction. At times, this will make it difficult to find out which resource the “waiter” is contending from the “holder”. This is easily resolved by looking at a combination of two monitors. MONITOR SESSION will tell you which user is waiting on a lock, and MONITOR LOCK will tell you the table that the user is currently trying to modify.

Identifying Conflicts

If you suspect a situation where one task is stopping another task from completing, Listing 5.6 shows a query you can run to find the objects that are involved in the locking conflict.

Listing 5.6 Finding the locking conflict.

waiters.sql - shows waiting tasks
COLUMN username     FORMAT a10
COLUMN lockwait     FORMAT a10
COLUMN sql_text     FORMAT a80
COLUMN object_owner FORMAT a14
COLUMN object       FORMAT a15

SELECT  b.username username,
        c.sid sid,
        c.owner object_owner,
        c.object object,
        b.lockwait,
        a.sql_text SQL
FROM v$sqltext a, v$session b, v$access c
WHERE
    a.address=b.sql_address
    AND
    a.hash_value=b.sql_hash_value
    AND
    b.sid = c.sid
    AND
    c.owner != 'SYS';

Listing 5.7 shows the sample output.

Listing 5.7 The query’s output.

USERNAME SID OBJECT_OWNER OBJECT    LOCKWAIT SQL
-------  --  ------------ ------    ---------------------------
BURLESON 36  EMP EMPLOYEE C4D450F9  update employee set status = 'Fired'
                                    where emp_nbr=16152
PAPAJ    15  EMP EMPLOYEE C3D320C8  delete from employee where
                                    emp_nbr=16152
TYTLER   11  EMP EMPLOYEE D3D4F9E0  update employee set salary =
                                    salary*.01 lock table customer in
                                    exclusive mode

Here we see a situation where Burleson and Papaj are waiting for Tytler’s update on the employee table to complete.

Using catblock.sql And utllockt.sql

Several locks scripts within $ORACLE_HOME/rdbms/admin can be used to see locks. To install the scripts, first enter SQL*DBA and then run catblock.sql followed by utllockt.sql.

catblock.sql creates the following views:

•  dba_waiters

•  dba_blockers

•  dba_dml_locks

•  dba_ddl_locks

•  dba_locks