Solved

DDL Locks

Posted on 2000-04-05
3
1,709 Views
Last Modified: 2012-06-27
Sometimes when I compile a package, Toad locks up and eventually times out with ORA-04021: timeout occurred while waiting for a lock on object ...

How do I know what session is blocking the compile? OEM lock manager does not seem to show anything.

Also, this particular package is used infrequently, has no package variables, and when it is used, the action takes only a few seconds and is always committed. It seems strange that there should be any contention for this object. What's happening here?
0
Comment
Question by:rkogelhe
3 Comments
 
LVL 4

Expert Comment

by:srikant033100
ID: 2686772
Try to increase the value of DML_LOCKS in the init.ora and u can get v$session,V$lock.
0
 
LVL 5

Expert Comment

by:sbenyo
ID: 2687836
This errors means that there are some other user accessing that object.

Try looking at v$access for the users (sessions) that access that object.
0
 
LVL 3

Accepted Solution

by:
junfeb earned 100 total points
ID: 2687841
Here is something you can use to look at locks - DML locks -

First of all as sys run $ORACLE_HOME/rdbms/admin/catblock.sql -
Some versions I have noticed that this script has a problem -

So you use this scirpt - Just a modified version of the original -

/*
/* $History: cb.sql $ */
/*  */
/* *****************  Version 1  ***************** */
/* User: Nr           Date: 5/18/98    Time: 12:36p */
/* Created in $/Oracle */
/* Nirmala -  */
/* version of Catblock.sql with errors corrected. */
/*  */
rem
rem $Header: /Oracle/cb.sql 1     5/18/98 12:36p Nr $ blocking.sql
rem
Rem Copyright (c) 1989 by Oracle Corporation
Rem NAME
Rem    catblock.sql
Rem  FUNCTION  -  create views of oracle locks
Rem  NOTES
Rem  MODIFIED
Rem     pgreenwa   08/14/95 -  bug #293557: optimize view queries
Rem     pgreenwa   05/10/95 -  fix dba_lock_internal
Rem     pgreenwa   04/25/95 -  add new vlock columns
Rem     drady      03/22/93 -  merge changes from branch 1.1.312.1
Rem     drady      03/18/93 -  fix 154271
Rem     glumpkin   10/17/92 -  renamed from BLOCKING.SQL
Rem     tpystyne   09/14/92 -  rename sid to session_id
Rem     jloaiza    07/30/92 -  fix for KGL change
Rem   tpystyne   05/27/92 - add dba_dml_locks and dba_ddl_locks views
Rem   jloaiza    05/24/91 - upgrade for v7
Rem   Loaiza     11/01/89 - Creation
Rem


/* this is an auxiliary view containing the KGL locks and pins */
drop view DBA_KGLLOCK;
create view DBA_KGLLOCK as
  select kgllkuse, kgllkhdl, kgllkmod, kgllkreq, 'Lock' kgllktype from x$kgllk
 union all
  select kglpnuse, kglpnhdl, kglpnmod, kglpnreq, 'Pin'  kgllktype from x$kglpn;

/*
 * DBA_LOCK has a row for each lock that is being held, and
 * one row for each outstanding request for a lock or latch.
 * The columns of DBA_LOCK are:
 *   session_id     - session holding or acquiring the lock
 *   type           - type of lock
 *   mode_held      - mode the lock is currently held in by the session
 *   mode_requested - mode that the lock is being requested in by the process
 *   lock_id1            - type specific identifier of the lock
 *   lock_id2            - type specific identifier of the lock
 *   last_convert   - time (in seconds) since last convert completed
 *   blocking_others     - is this lock blocking other locks
 */
drop synonym DBA_LOCKS;
drop view DBA_LOCKS;
drop view DBA_LOCK;
create view DBA_LOCK as
  select
      sid session_id,
      decode(type,
            'MR', 'Media Recovery',
            'RT', 'Redo Thread',
            'UN', 'User Name',
            'TX', 'Transaction',
            'TM', 'DML',
            'UL', 'PL/SQL User Lock',
            'DX', 'Distributed Xaction',
            'CF', 'Control File',
            'IS', 'Instance State',
            'FS', 'File Set',
            'IR', 'Instance Recovery',
            'ST', 'Disk Space Transaction',
            'TS', 'Temp Segment',
            'IV', 'Library Cache Invalidation',
            'LS', 'Log Start or Switch',
            'RW', 'Row Wait',
            'SQ', 'Sequence Number',
            'TE', 'Extend Table',
            'TT', 'Temp Table',
            type) lock_type,
      decode(lmode,
            0, 'None',           /* Mon Lock equivalent */
            1, 'Null',           /* N */
            2, 'Row-S (SS)',     /* L */
            3, 'Row-X (SX)',     /* R */
            4, 'Share',          /* S */
            5, 'S/Row-X (SSX)',  /* C */
            6, 'Exclusive',      /* X */
            to_char(lmode)) mode_held,
         decode(request,
            0, 'None',           /* Mon Lock equivalent */
            1, 'Null',           /* N */
            2, 'Row-S (SS)',     /* L */
            3, 'Row-X (SX)',     /* R */
            4, 'Share',          /* S */
            5, 'S/Row-X (SSX)',  /* C */
            6, 'Exclusive',      /* X */
            to_char(request)) mode_requested,
         to_char(id1) lock_id1, to_char(id2) lock_id2,
       ctime last_convert,
       decode(block,
              0, 'Not Blocking',  /* Not blocking any other processes */
            1, 'Blocking',      /* This lock blocks other processes */
            2, 'Global',        /* This lock is global, so we can't tell */
            to_char(block)) blocking_others
      from v$lock;              
create synonym DBA_LOCKS for DBA_LOCK;

/*
 * DBA_LOCK_INTERNAL has a row for each lock or latch that is being held, and
 * one row for each outstanding request for a lock or latch.
 * The columns  of DBA_LOCK_INTERNAL are:
 *   session_id     - session holding or acquiring the lock
 *   type           - type of lock (DDL, LATCH, etc.)
 *   mode_held      - mode the lock is currently held in by the session
 *   mode_requested - mode that the lock is being requested in by the process
 *   lock_id1            - type specific identifier of the lock
 *   lock_id2            - type specific identifier of the lock
 *
 * NOTE: this view can be very, very slow depending on the size of your
 *       shared pool area and database activity.
 */
drop view DBA_LOCK_INTERNAL;
create view DBA_LOCK_INTERNAL as
  select
      sid session_id,
      decode(type,
            'MR', 'Media Recovery',
            'RT', 'Redo Thread',
            'UN', 'User Name',
            'TX', 'Transaction',
            'TM', 'DML',
            'UL', 'PL/SQL User Lock',
            'DX', 'Distributed Xaction',
            'CF', 'Control File',
            'IS', 'Instance State',
            'FS', 'File Set',
            'IR', 'Instance Recovery',
            'ST', 'Disk Space Transaction',
            'TS', 'Temp Segment',
            'IV', 'Library Cache Invalidation',
            'LS', 'Log Start or Switch',
            'RW', 'Row Wait',
            'SQ', 'Sequence Number',
            'TE', 'Extend Table',
            'TT', 'Temp Table',
            type) lock_type,
      decode(lmode,
            0, 'None',           /* Mon Lock equivalent */
            1, 'Null',           /* N */
            2, 'Row-S (SS)',     /* L */
            3, 'Row-X (SX)',     /* R */
            4, 'Share',          /* S */
            5, 'S/Row-X (SSX)',  /* C */
            6, 'Exclusive',      /* X */
            to_char(lmode)) mode_held,
         decode(request,
            0, 'None',           /* Mon Lock equivalent */
            1, 'Null',           /* N */
            2, 'Row-S (SS)',     /* L */
            3, 'Row-X (SX)',     /* R */
            4, 'Share',          /* S */
            5, 'S/Row-X (SSX)',  /* C */
            6, 'Exclusive',      /* X */
            to_char(request)) mode_requested,
         to_char(id1) lock_id1, to_char(id2) lock_id2
      from v$lock              
 union all                                          /* procs holding latches */
  select s.sid, 'LATCH', 'Exclusive', 'None', rawtohex(laddr), ' '
    from v$process p, v$session s, v$latchholder h
   where h.pid  = p.pid                       /* 6 = exclusive, 0 = not held */
    and  p.addr = s.paddr
 union all                                         /* procs waiting on latch */
  select sid, 'LATCH', 'None', 'Exclusive', latchwait,' '
     from v$session s, v$process p
    where latchwait is not null
     and  p.addr = s.paddr
 union all                                            /* library cache locks */
  select  s.sid,
    decode(ob.kglhdnsp, 0, 'Cursor', 1, 'Table/Procedure', 2, 'Body',
           3, 'trigger', 4, 'Index', 5, 'Cluster', to_char(ob.kglhdnsp))
        || ' Definition ' || lk.kgllktype,
    decode(lk.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
         to_char(lk.kgllkmod)),
    decode(lk.kgllkreq,  0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
         to_char(lk.kgllkreq)),
    decode(ob.kglnaown, null, '', ob.kglnaown || '.') || ob.kglnaobj ||
    decode(ob.kglnadlk, null, '', '@' || ob.kglnadlk),
    rawtohex(lk.kgllkhdl)
   from v$session s, x$kglob ob, dba_kgllock lk
     where lk.kgllkhdl = ob.kglhdadr
      and  lk.kgllkuse = s.saddr;
 
/*
 * DBA_DML_LOCKS has a row for each DML lock that is being held, and
 * one row for each outstanding request for a DML lock. It is subset
 * of DBA_LOCKS
 */

drop view DBA_DML_LOCKS;

create view DBA_DML_LOCKS as
  select
      sid session_id,
        u.name owner,
        o.name,
      decode(lmode,
            0, 'None',           /* Mon Lock equivalent */
            1, 'Null',           /* N */
            2, 'Row-S (SS)',     /* L */
            3, 'Row-X (SX)',     /* R */
            4, 'Share',          /* S */
            5, 'S/Row-X (SSX)',  /* C */
            6, 'Exclusive',      /* X */
            'Invalid') mode_held,
         decode(request,
            0, 'None',           /* Mon Lock equivalent */
            1, 'Null',           /* N */
            2, 'Row-S (SS)',     /* L */
            3, 'Row-X (SX)',     /* R */
            4, 'Share',          /* S */
            5, 'S/Row-X (SSX)',  /* C */
            6, 'Exclusive',      /* X */
            'Invalid') mode_requested,
       l.ctime last_convert,
       decode(block,
              0, 'Not Blocking',  /* Not blocking any other processes */
            1, 'Blocking',      /* This lock blocks other processes */
            2, 'Global',        /* This lock is global, so we can't tell */
            to_char(block)) blocking_others
      from v$lock l, obj$ o, user$ u
      where l.id1 = o.obj#
      and   o.owner# = u.user#
      and   l.type = 'TM';

/*
 * DBA_DDL_LOCKS has a row for each DDL lock that is being held, and
 * one row for each outstanding request for a DDL lock. It is subset
 * of DBA_LOCKS
 */

drop view DBA_DDL_LOCKS;

create view DBA_DDL_LOCKS as
  select  s.sid session_id,
          substr(ob.kglnaown,1,30) owner,
          substr(ob.kglnaobj,1,30) name,
    decode(ob.kglhdnsp, 0, 'Cursor', 1, 'Table/Procedure', 2, 'Body',
           3, 'Trigger', 4, 'Index', 5, 'Cluster', 'Unknown') type,
    decode(lk.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
         'Unknown') mode_held,
    decode(lk.kgllkreq,  0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
         'Unknown') mode_requested
   from v$session s, x$kglob ob, x$kgllk lk
   where lk.kgllkhdl = ob.kglhdadr
   and   lk.kgllkuse = s.saddr
   and   ob.kglhdnsp != 0;

/*
 * Show all the sessions waiting for locks and the session that holds the
 * lock.
 */
drop view DBA_WAITERS;

create view DBA_WAITERS as
 select /*+ all_rows */ w.session_id  waiting_session,
      h.session_id  holding_session,
      w.lock_type,
      h.mode_held,
      w.mode_requested,
      w.lock_id1,
      w.lock_id2
  from dba_locks w, dba_locks h
 where h.blocking_others = 'Blocking'
  and  h.mode_held      !=  'None'
  and  h.mode_held      !=  'Null'
  and  w.mode_requested !=  'None'
  and  w.lock_type       =  h.lock_type
  and  w.lock_id1       =  h.lock_id1
  and  w.lock_id2        =  h.lock_id2;

/*
 * Show all the sessions that have someone waiting on a lock they hold, but
 * that are not themselves waiting on a lock.
 */
drop view DBA_BLOCKERS;

create view DBA_BLOCKERS as
 select /*+ all_rows */ holding_session from dba_waiters
minus
 select /*+ all_rows */ session_id from dba_locks w
   where w.mode_requested != 'None';

=====================
This will create a set of very useful views .

=============================

To look at DML locks that are on your system - you can run the following script as often as you want -

set pagesize 0
set lines 80
spool locks

col a1 for a10 heading 'Username'
col a2 for a10 heading 'OS|Process'
col a3 for 9999999 heading 'Process|ID'
col a4 for a30 heading 'Table'
col a5 for a10 heading 'Mode'


select s.username a1, upper(s.osuser) a2, s.process a3, l.name a4,
       l.mode_held a5
from dba_dml_locks l, v$session s
where l.session_id = s.sid
/
spool off
=====================================

Also if you want to know the ddl locks , like sessions holding compile locks,
do

select * from dba_ddl_locks;

==========================================

You can also run $ORACLE_HOME/rdbms/admin/utllockt to see what's blocking .

====================================
Thanks.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now