Oracle Locks   «Prev  Next»
Lesson 8 The lock utility scripts
ObjectiveRun the lock utility scripts

Lock Utility Scripts (How to Run)

There are several lock scripts within $ORACLE_HOME/rdbms/admin that can be used to see lock activity and to detect lock contention.
To install the scripts:
  1. Enter Server Manager (SVRMGRL).
  2. Run catblock.sql.
  3. Then run utllockt.sql.

These scripts enhance the basic Oracle lock views, and allow for the easy detection of tasks that are holding locks, and tasks that are waiting on locks to be released.

The catblock.sql script

The catblock.sql script creates the following useful views:
  1. dba_waiters
  2. dba_blockers
  3. dba_dml_locks
  4. dba_ddl_locks
  5. dba_locks

REM alllocks.sql - shows all locks in the database.
REM written by Thomas Hauck
 
set linesize 132
set pagesize 60
 
spool /tmp/alllocks
 
column owner          format a10;
column name           format a15;
column mode_held      format a10;
column mode_requested format a10;
column type           format a15;
column lock_id1       format a10;
column lock_id2       format a10;
 
prompt note that $oracle_home/rdbma/admin/catblock.sql
prompt must be run before this script functions . . .
 
prompt querying dba_waiters . . .
select
 waiting_session,
 holding_session,
 lock_type,
 mode_held,
 mode_requested,
 lock_id1,
 lock_id2
from 
   sys.dba_waiters;
 
prompt querying dba_blockers . . .
select
 holding_session
from 
   sys.dba_blockers;
 
prompt querying dba_dml_locks . . .
select
 session_id,
 owner,
 name,
 mode_held,
 mode_requested
from 
   sys.dba_dml_locks;
 
prompt querying dba_ddl_locks . . .
select
 session_id,
 owner,
 name,
 type,
 mode_held,
 mode_requested
from 
   sys.dba_ddl_locks;
 
 
prompt querying dba_locks . . .
select
 session_id,
 lock_type,
 mode_held,
 mode_requested,
 lock_id1,
 lock_id2
from 
   sys.dba_locks;
SQL > @alllocks
Note that $ORACLE_HOME/rdbms/admin/catblock.sql
must be run before this script functions . . .
Querying dba_waiters . . .
 
no rows selected
 
Querying dba_blockers . . .
 
no rows selected
 
Querying dba_dml_locks . . .
 
SESSION_ID OWNER      NAME           MODE_HELD  MODE_REQUE
---------- ---------- -------------- ---------- ----------
        19 RPT        RPT_EXCEPTIONS Row-X (SX) None
 
Querying dba_ddl_locks . . .
 
SESSION                                                 MODE_    MODE_
_ID OWNER NAME TYPE HELD REQUE ------- ----- --------------- -------------------- -------- ------- 13 RPT SHP_PRE_INS_UPD Table/Procedure Null None
_PROC 13 SYS STANDARD Body Null None 14 SYS STANDARD Body Null None 13 SYS DBMS_STANDARD Table/Procedure Null None 14 SYS DBMS_STANDARD Table/Procedure Null None 13 SYS DBMS_STANDARD Body Null None 14 SYS DBMS_STANDARD Body Null None 13 SYS STANDARD Table/Procedure Null None 14 SYS STANDARD Table/Procedure Null None 9 rows selected. Querying dba_locks . . . SESSION_ID LOCK_TYPE MODE_HELD MODE_REQUE LOCK_ID1 LOCK_ID2 ---------- ---------------- ---------- ---------- ---------- --------- 2 Media Recovery Share None 32 0 2 Media Recovery Share None 31 0 2 Media Recovery Share None 30 0 2 Media Recovery Share None 29 0 2 Media Recovery Share None 28 0 2 Media Recovery Share None 27 0 2 Media Recovery Share None 26 0 2 Media Recovery Share None 25 0 2 Media Recovery Share None 24 0 2 Media Recovery Share None 23 0 2 Media Recovery Share None 22 0 2 Media Recovery Share None 21 0 2 Media Recovery Share None 20 0 2 Media Recovery Share None 19 0 2 Media Recovery Share None 18 0 2 Media Recovery Share None 17 0 2 Media Recovery Share None 16 0 2 Media Recovery Share None 15 0 2 Media Recovery Share None 14 0 2 Media Recovery Share None 13 0 2 Media Recovery Share None 12 0 2 Media Recovery Share None 11 0 2 Media Recovery Share None 10 0 2 Media Recovery Share None 9 0 2 Media Recovery Share None 8 0 2 Media Recovery Share None 7 0 2 Media Recovery Share None 6 0 2 Media Recovery Share None 5 0 2 Media Recovery Share None 4 0 2 Media Recovery Share None 3 0 2 Media Recovery Share None 2 0 2 Media Recovery Share None 1 0 3 Redo Thread Exclusive None 1 0 14 PS Null None 0 0 14 PS Null None 0 1 19 DML Row-X (SX) None 1457 0 36 rows selected.

The script below can be used whenever you suspect that locks are impeding performance. This script interrogates all of the views that were created in catblock.sql. Click the View Code button to see the script and the output.

The utllockt.sql script

The utllockt.sql script creates a view called lock_holders that can then be queried to see all sessions that are waiting on locks. Beware, however, this view creates a temporary table and can run slowly. The script and output are below:

column waiting_session format a8
 
select lpad(' ',3*(level-1)) ||
waiting_session waiting_session, lock_type, mode_requested, mode_held, lock_id1, lock_id2 from lock_holders connect by prior waiting_session = holding_session start with holding_session is null;

Output

Now that we see the Oracle utility scripts for locks, let's explore how we can do our own locking within Oracle with the dbms_lock package.

Lock Utility Scripts - Exercise

First, click the Exercise link below and check your knowledge of lock utility scripts.
Lock Utility Scripts - Exercise