Re: lock problem ???

From: Jared Still <jkstill_at_cybcon.com>
Date: Wed, 31 Oct 2001 21:53:02 -0800
Message-ID: <F001.003B9FFE.20011031212521@fatcity.com>

On Tuesday 30 October 2001 14:45, Janet Linsy wrote:

> Hi all,
>
> About the locking, could someone show me some script
> that shows who is locking others and who is being
> locked?  And how to get the related locking or locked
> queries?
>
> Thank you!
>
> Janet
>

Well, here's the one I use. It has the disadvantage of creating a temporary table the first time it's run, and truncating it every time thereafter.

It has the advantage of being very fast. Queries against the locking views can otherwise take a very long time on occasion.

Jared

--**************************************

• showlock.sql - show all user locks
• speeded up greatly by changing order of where clause,
• and using all_objects instead of dba_objects
• jks 04/09/1997 - show lock addresses and lockwait
• jks 04/09/1997 - outer join on all_objects
• encountered situation on 7.2
• where there was a lock with no
• matching object_id
• jks 02/24/1999 - join to dba_waiters to show waiters and blockers
• jks 08/09/2001 - rewritten using PL/SQL and DBMS_SQL
• - this is done to create temporary tables to collect
• - the lock data, and then query the tmp tables
• - *much* faster than querying the data dictionary
• - directly.
• - Using PL/SQL provides away to prevent annoying
• - error messages like ORA-00955

set trimspool on
ttitle off
set linesize 155
set pagesize 60

column osuser heading 'OS|Username' format a7 truncate
column process heading 'OS|Process' format a7 truncate
column machine heading 'OS|Machine' format a10 truncate
column program heading 'OS|Program' format a25 truncate

column object heading 'Database|Object' format a25 truncate

column lock_type heading 'Lock|Type' format a4 truncate
column mode_held heading 'Mode|Held' format a15 truncate
column mode_requested heading 'Mode|Requested' format a10 truncate

column sid heading 'SID' format 999
column username heading 'Oracle|Username' format a7 truncate column image heading 'Active Image' format a20 truncate column sid format 99999
col waiting_session head 'WAITER' format 9999 col holding_session head 'BLOCKER' format 9999

• the script ORACLE_HOME/rdbms/admin/catblock.sql must
• be run by SYS for this script to function
  --
  
• public synonyms needed for this to work
• create public synonym dba_locks for sys.dba_locks
• create public synonym dba_waiters for sys.dba_waiters
• uncomment to drop the tmp tables
  --
  --drop table dba_locks_tab;
  --drop table dba_waiters_tab;
  --drop table dba_locks_held_tab;
  

set feed off

declare

        c integer default 0;
        v_retval integer;
        v_stmt varchar2(4000);
        table_exists exception;

        pragma exception_init( table_exists, -955 );

        sq char(1) := chr(39);
        lf char(1) := chr(10);

begin

        v_stmt := 'create table dba_locks_tab '
                || 'as '
                || 'select * '
                || 'from dba_locks '
                || 'where 1=2';

        -- create table dba_locks_tab
        c := dbms_sql.open_cursor;
        begin
                dbms_sql.parse( c, v_stmt,dbms_sql.native);
                v_retval := dbms_sql.execute(c); 
                dbms_sql.close_cursor(c);
        exception
        when table_exists then
                -- ignores table create errors
                dbms_sql.close_cursor(c);
        when others then
                raise;
        end;

        -- truncate dba_locks_tab
        v_stmt := 'truncate table dba_locks_tab';
        c := dbms_sql.open_cursor;
        dbms_sql.parse( c, v_stmt,dbms_sql.native);
        v_retval := dbms_sql.execute(c); 
        dbms_sql.close_cursor(c);

        v_stmt := 'create table dba_waiters_tab '
                || 'as '
                || 'select * '
                || 'from dba_waiters '
                || 'where 1=2 ';

        -- create table dba_waiters_tab
        c := dbms_sql.open_cursor;
        begin
                dbms_sql.parse( c, v_stmt,dbms_sql.native);
                v_retval := dbms_sql.execute(c); 
                dbms_sql.close_cursor(c);
        exception
        when table_exists then
                -- ignores table create errors
                dbms_sql.close_cursor(c);
        when others then
                raise;
        end;

        -- truncate dba_locks_tab
        v_stmt := 'truncate table dba_waiters_tab';
        c := dbms_sql.open_cursor;
        dbms_sql.parse( c, v_stmt,dbms_sql.native);
        v_retval := dbms_sql.execute(c); 
        dbms_sql.close_cursor(c);

end;
/

declare

        c integer default 0;
        v_retval integer;
        v_stmt varchar2(4000);
        v_locks_held_sql varchar2(4000);
        table_exists exception;

        pragma exception_init( table_exists, -955 );

        sq char(1) := chr(39);
        lf char(1) := chr(10);

begin

        v_locks_held_sql := 'select /*+ ordered */ ' || lf
                || 'c.sid, ' || lf
                || 'lock_waiter.waiting_session, ' || lf
                || 'lock_blocker.holding_session, ' || lf
                || 'c.program, ' || lf
                || 'c.osuser, ' || lf
                || 'c.machine, ' || lf
                || 'c.process, ' || lf
                || 'decode(u.name, ' || lf
                ||      'null,' || sq || sq || ', ' || lf
                ||      'u.name'        || '||' || sq || '.' || sq || '||' || 'o.name 
' || lf
                || ') object, ' || lf
                || 'c.username, ' || lf
                || 'b.type, ' || lf
                || 'b.lmode, ' || lf
                || 'b.request ' || lf
        || 'from ' || lf
                || 'v$lock b' || lf
                || ',v$session c ' || lf
                || ',sys.user$ u ' || lf
                || ',sys.obj$ o ' || lf
                || ', dba_waiters_tab lock_blocker ' || lf
                || ', dba_waiters_tab lock_waiter ' || lf
        || 'where ' || lf
        || 'b.sid = c.sid ' || lf
        || 'and u.user# = c.user# ' || lf
        || 'and o.obj#(+) = b.id1 ' || lf
        || 'and lock_blocker.waiting_session(+) = c.sid ' || lf
        || 'and lock_waiter.holding_session(+) = c.sid ' || lf
        || 'and c.username !=' || sq || 'SYS' || sq  || lf;


        v_stmt := 'create table dba_locks_held_tab '
                || 'as ' || v_locks_held_sql
                || ' and 1 = 2';

        -- create table dba_locks_held_tab
        c := dbms_sql.open_cursor;
        begin
                dbms_sql.parse( c, v_stmt,dbms_sql.native);
                v_retval := dbms_sql.execute(c); 
                dbms_sql.close_cursor(c);
        exception
        when table_exists then
                -- ignores table create errors
                dbms_sql.close_cursor(c);
        when others then
                raise;
        end;

        -- truncate dba_locks_held_tab
        v_stmt := 'truncate table dba_locks_held_tab';
        c := dbms_sql.open_cursor;
        dbms_sql.parse( c, v_stmt,dbms_sql.native);
        v_retval := dbms_sql.execute(c); 
        dbms_sql.close_cursor(c);

end;
/

declare

        c integer default 0;
        v_retval integer;
        v_stmt varchar2(4000);
        v_locks_held_sql varchar2(4000);
        table_exists exception;

        pragma exception_init( table_exists, -955 );
        sq char(1) := chr(39);
        lf char(1) := chr(10);

begin

        v_locks_held_sql := 'select /*+ ordered */ ' || lf
                || 'c.sid, ' || lf
                || 'lock_waiter.waiting_session, ' || lf
                || 'lock_blocker.holding_session, ' || lf
                || 'c.program, ' || lf
                || 'c.osuser, ' || lf
                || 'c.machine, ' || lf
                || 'c.process, ' || lf
                || 'decode(u.name, ' || lf
                ||      'null,' || sq || sq || ', ' || lf
                ||      'u.name'        || '||' || sq || '.' || sq || '||' || 'o.name 
' || lf
                || ') object, ' || lf
                || 'c.username, ' || lf
                || 'b.type, ' || lf
                || 'b.lmode, ' || lf
                || 'b.request ' || lf
        || 'from ' || lf
                || 'v$lock b' || lf
                || ',v$session c ' || lf
                || ',sys.user$ u ' || lf
                || ',sys.obj$ o ' || lf
                || ', dba_waiters_tab lock_blocker ' || lf
                || ', dba_waiters_tab lock_waiter ' || lf
        || 'where ' || lf
        || 'b.sid = c.sid ' || lf
        || 'and u.user# = c.user# ' || lf
        || 'and o.obj#(+) = b.id1 ' || lf
        || 'and lock_blocker.waiting_session(+) = c.sid ' || lf
        || 'and lock_waiter.holding_session(+) = c.sid ' || lf
        || 'and c.username !=' || sq || 'SYS' || sq  || lf;
        -- insert into dba_locks_tab
        insert into dba_locks_tab
        select * from dba_locks;

        -- insert into dba_waiters_tab
        insert into dba_waiters_tab
        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_tab w, dba_locks_tab 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;

        -- insert into dba_locks_held_tab
        v_stmt := 'insert into dba_locks_held_tab '
                || v_locks_held_sql;
        c := dbms_sql.open_cursor;
        dbms_sql.parse( c, v_stmt,dbms_sql.native);
        v_retval := dbms_sql.execute(c); 
        dbms_sql.close_cursor(c);

end;
/

commit;

set feed on

select

        l.sid,
        l.waiting_session,
        l.holding_session,
        l.program,
        l.osuser,
        l.machine,
        l.process,
        l.object,
        l.username,
        decode
        (
                l.type,
                'BL', 'Buffer hash table instance lock',
                'CF', 'Control file schema global enqueue lock',
                'CI', 'Cross-instance function invocation instance lock',
                'CU', 'Cursor bind lock',
                'DF', 'Data file instance lock',
                'DL', 'direct loader parallel index create lock',
                'DM', 'Mount/startup db primary/secondary instance lock',
                'DR', 'Distributed recovery process lock',
                'DX', 'Distributed transaction entry lock',
                'FS', 'File set lock',
                'IN', 'Instance number lock',
                'IR', 'Instance recovery serialization global enqueue lock',
                'IS', 'Instance state lock',
                'IV', 'Library cache invalidation instance lock',
                'JQ', 'Job queue lock',
                'KK', 'Thread kick lock',
                'LA','Library cache lock instance lock (A..P=namespace);',
                'LB','Library cache lock instance lock (A..P=namespace);',
                'LC','Library cache lock instance lock (A..P=namespace);',
                'LD','Library cache lock instance lock (A..P=namespace);',
                'LE','Library cache lock instance lock (A..P=namespace);',
                'LF','Library cache lock instance lock (A..P=namespace);',
                'LG','Library cache lock instance lock (A..P=namespace);',
                'LH','Library cache lock instance lock (A..P=namespace);',
                'LI','Library cache lock instance lock (A..P=namespace);',
                'LJ','Library cache lock instance lock (A..P=namespace);',
                'LK','Library cache lock instance lock (A..P=namespace);',
                'LL','Library cache lock instance lock (A..P=namespace);',
                'LM','Library cache lock instance lock (A..P=namespace);',
                'LN','Library cache lock instance lock (A..P=namespace);',
                'LO','Library cache lock instance lock (A..P=namespace);',
                'LP','Library cache lock instance lock (A..P=namespace);',
                'MM', 'Mount definition global enqueue lock',
                'MR', 'Media recovery lock',
                'NA', 'Library cache pin instance lock (A..Z=namespace)',
                'NB', 'Library cache pin instance lock (A..Z=namespace)',
                'NC', 'Library cache pin instance lock (A..Z=namespace)',
                'ND', 'Library cache pin instance lock (A..Z=namespace)',
                'NE', 'Library cache pin instance lock (A..Z=namespace)',
                'NF', 'Library cache pin instance lock (A..Z=namespace)',
                'NG', 'Library cache pin instance lock (A..Z=namespace)',
                'NH', 'Library cache pin instance lock (A..Z=namespace)',
                'NI', 'Library cache pin instance lock (A..Z=namespace)',
                'NJ', 'Library cache pin instance lock (A..Z=namespace)',
                'NK', 'Library cache pin instance lock (A..Z=namespace)',
                'NL', 'Library cache pin instance lock (A..Z=namespace)',
                'NM', 'Library cache pin instance lock (A..Z=namespace)',
                'NN', 'Library cache pin instance lock (A..Z=namespace)',
                'NO', 'Library cache pin instance lock (A..Z=namespace)',
                'NP', 'Library cache pin instance lock (A..Z=namespace)',
                'NQ', 'Library cache pin instance lock (A..Z=namespace)',
                'NR', 'Library cache pin instance lock (A..Z=namespace)',
                'NS', 'Library cache pin instance lock (A..Z=namespace)',
                'NT', 'Library cache pin instance lock (A..Z=namespace)',
                'NU', 'Library cache pin instance lock (A..Z=namespace)',
                'NV', 'Library cache pin instance lock (A..Z=namespace)',
                'NW', 'Library cache pin instance lock (A..Z=namespace)',
                'NX', 'Library cache pin instance lock (A..Z=namespace)',
                'NY', 'Library cache pin instance lock (A..Z=namespace)',
                'NZ', 'Library cache pin instance lock (A..Z=namespace)',
                'PF', 'Password File lock',
                'PI', 'Parallel operation locks',
                'PS', 'Parallel operation locks',
                'PR', 'Process startup lock',
                'QA','Row cache instance lock (A..Z=cache)',
                'QB','Row cache instance lock (A..Z=cache)',
                'QC','Row cache instance lock (A..Z=cache)',
                'QD','Row cache instance lock (A..Z=cache)',
                'QE','Row cache instance lock (A..Z=cache)',
                'QF','Row cache instance lock (A..Z=cache)',
                'QG','Row cache instance lock (A..Z=cache)',
                'QH','Row cache instance lock (A..Z=cache)',
                'QI','Row cache instance lock (A..Z=cache)',
                'QJ','Row cache instance lock (A..Z=cache)',
                'QK','Row cache instance lock (A..Z=cache)',
                'QL','Row cache instance lock (A..Z=cache)',
                'QM','Row cache instance lock (A..Z=cache)',
                'QN','Row cache instance lock (A..Z=cache)',
                'QP','Row cache instance lock (A..Z=cache)',
                'QQ','Row cache instance lock (A..Z=cache)',
                'QR','Row cache instance lock (A..Z=cache)',
                'QS','Row cache instance lock (A..Z=cache)',
                'QT','Row cache instance lock (A..Z=cache)',
                'QU','Row cache instance lock (A..Z=cache)',
                'QV','Row cache instance lock (A..Z=cache)',
                'QW','Row cache instance lock (A..Z=cache)',
                'QX','Row cache instance lock (A..Z=cache)',
                'QY','Row cache instance lock (A..Z=cache)',
                'QZ','Row cache instance lock (A..Z=cache)',
                'RT', 'Redo thread global enqueue lock',
                'SC', 'System commit number instance lock',
                'SM', 'SMON lock',
                'SN', 'Sequence number instance lock',
                'SQ', 'Sequence number enqueue lock',
                'SS', 'Sort segment locks',
                'ST', 'Space transaction enqueue lock',
                'SV', 'Sequence number value lock',
                'TA', 'Generic enqueue lock',
                'TS', 'Temporary segment enqueue lock (ID2=0)',
                'TS', 'New block allocation enqueue lock (ID2=1)',
                'TT', 'Temporary table enqueue lock',
                'UN', 'User name lock',
                'US', 'Undo segment DDL lock',
                'WL', 'Being-written redo log instance lock',
                l.type
        ) lock_type,
        decode
        (
                l.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 (SRX)',  /* C */
                6, 'Exclusive',      /* X */
                to_char(l.lmode)
        ) mode_held,
        decode
        (
                l.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(l.request)
        ) mode_requested

from dba_locks_held_tab l
order by sid
/

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Jared Still
  INET: jkstill_at_cybcon.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Wed Oct 31 2001 - 23:53:02 CST