ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【视图】使用DBA_DDL_LOCKS视图获得DDL锁定信息

【视图】使用DBA_DDL_LOCKS视图获得DDL锁定信息

原创 Linux操作系统 作者:secooler 时间:2009-08-10 14:56:54 0 删除 编辑
1.查询所有DDL lock的信息
sys@ora10g> select * from dba_ddl_locks;

SESS OWNER NAME                  TYPE                 MODE_HELD MODE_REQU
---- ----- --------------------- -------------------- --------- ---------
 162 SYS   SCHEDULER$_INSTANCE_S Table/Procedure/Type Null      None
 161 SYS   AQ$_ALERT_QT_E        10                   Null      None
 161 SYS   DBMS_HA_ALERTS_PRVT   Body                 Null      None
 161 SYS   DBMS_PRVT_TRACE       Table/Procedure/Type Null      None
 161 SYS   PLITBLM               Table/Procedure/Type Null      None
 161 SYS   PLITBLM               Table/Procedure/Type Null      None
 161 SYS   STANDARD              Table/Procedure/Type Null      None
 161 SYS   DBMS_HA_ALERTS_PRVT   Table/Procedure/Type Null      None
 161 SYS   DBMS_PRVT_TRACE       Body                 Null      None
 161 SYS   DBMS_APPLICATION_INFO Table/Procedure/Type Null      None
 161 SYS   STANDARD              Body                 Null      None
 161 SYS   DBMS_APPLICATION_INFO Body                 Null      None
 161 SYS   DBMS_BACKUP_RESTORE   Body                 Null      None
 161 SYS   ALERT_QUE_R           23                   Null      None
 161 SYS   ALERT_QUE_R           23                   Null      None
 161 SYS   DBMS_BACKUP_RESTORE   Table/Procedure/Type Null      None
 161 SYS   DBMS_RCVMAN           Body                 Null      None
 158 SYS   DICTIONARY_OBJ_OWNER  Table/Procedure/Type Null      None
 158 SYS   DBMS_STANDARD         Table/Procedure/Type Null      None
 158 SYS   DBMS_OUTPUT           Body                 Null      None
 158 SYS   PLITBLM               Table/Procedure/Type Null      None
 158 SYS   DICTIONARY_OBJ_TYPE   Table/Procedure/Type Null      None
 158 SEC   SEC                   18                   Null      None
 158 SYS   DATABASE              18                   Null      None
 158 SYS   DBMS_OUTPUT           Table/Procedure/Type Null      None
 158 SYS   DBMS_APPLICATION_INFO Table/Procedure/Type Null      None
 158 SYS   AW_DROP_PROC          Table/Procedure/Type Null      None
 158 SYS   DBMS_APPLICATION_INFO Body                 Null      None
 158 SYS   DICTIONARY_OBJ_NAME   Table/Procedure/Type Null      None
 145 SYS   DBMS_OUTPUT           Body                 Null      None
 145 SYS   PLITBLM               Table/Procedure/Type Null      None
 145 SYS   DATABASE              18                   Null      None
 145 SYS   DBMS_OUTPUT           Table/Procedure/Type Null      None
 145 SYS   OBJECT_GRANT          Table/Procedure/Type Null      None

34 rows selected.

2.如果提示没有这个视图,可以在sys用户下执行$ORACLE_HOME/rdbms/admin/catblock.sql脚本进行创建(这个脚本还包含其他一些非常有意义的锁相关视图)
sys@ora10g> conn / as sysdba
Connected.
sys@ora10g> @?/rdbms/admin/catblock.sql
这里省略创建过程

3.Oracle 10g官方文档中关于视图DBA_DDL_LOCKS的描述如下

DBA_DDL_LOCKS

DBA_DDL_LOCKS lists all DDL locks held in the database and all outstanding requests for a DDL lock.

Column Datatype NULL Description
SESSION_ID NUMBER
Session identifier
OWNER VARCHAR2(30)
Owner of the lock
NAME VARCHAR2(30)
Name of the lock
TYPE VARCHAR2(40)
Lock type:
  • Cursor

  • Table/Procedure/Type

  • Body

  • Trigger

  • Index

  • Cluster

  • Java Source

  • Java Resource

  • Java Data

MODE_HELD VARCHAR2(9)
Lock mode:
  • None

  • Null

  • Share

  • Exclusive

MODE_REQUESTED VARCHAR2(9)
Lock request type:
  • None

  • Null

  • Share

  • Exclusive



4.打印一下catblock.sql脚本的内容,这个创建脚本其实可以当做一个参考文档来用,尤其是其中关于锁类型的描述。
ora10g@testdb /home/oracle$ cat $ORACLE_HOME/rdbms/admin/catblock.sql
rem
rem $Header: catblock.sql 07-jan-2004.12:19:20 kigoyal Exp $ blocking.sql
rem
Rem Copyright (c) 1989, 2004, Oracle Corporation.  All rights reserved.
Rem NAME
Rem    catblock.sql
Rem  FUNCTION  -  create views of oracle locks
Rem  NOTES
Rem  MODIFIED
Rem     kigoyal    01/06/04  - DBA_WAITERS/BLOCKERS to use "enq:%"
Rem     gviswana   05/24/01  - CREATE OR REPLACE SYNONYM
Rem     jdavison   10/10/00  - Fix dba_lock_internal view
Rem     vsaksena   07/07/00  - Optimize views DBA_BLOCKERS, DBA_WAITERS
Rem     arithikr   02/10/00 -  878668: Fix DBA_WAITERS view for OPS environment
Rem     sparrokk   12/17/99  - 1040651: Use v$lock defn without hint
Rem                            in dba_dml_locks
Rem     mjungerm   06/15/99 -  add java shared data object type
Rem     nireland   03/17/98 -  Add synonyms for DBA_WAITERS, DBA_BLOCKERS
Rem                            and others. #605559
Rem     agardner   04/02/97 -  bug #226646: remove comment
Rem     jwijaya    03/19/97 -  support types
Rem     asurpur    04/08/96 -  Dictionary Protection Implementation
Rem     pgreenwa   08/14/95 -  bug #293557: optimize view queries
Rem     wmaimone   01/04/96 -  7.3 merge
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 */
create or replace 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;
create or replace public synonym DBA_KGLLOCK for DBA_KGLLOCK;
grant select on DBA_KGLLOCK to select_catalog_role;

/*
 * 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;
create or replace 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 or replace public synonym DBA_LOCK for DBA_LOCK;
grant select on DBA_LOCK to select_catalog_role;
create or replace public 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.
 */
create or replace 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                /* processes waiting on or holding enqueues */
 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', rawtohex(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/Type', 2, 'Body',
             3, 'trigger', 4, 'Index', 5, 'Cluster', 13, 'Java Source',
             14, 'Java Resource', 32, 'Java Data', 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;
create or replace public synonym DBA_LOCK_INTERNAL for DBA_LOCK_INTERNAL;
grant select on DBA_LOCK_INTERNAL to select_catalog_role;

/*
 * 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
 */

create or replace 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 (select l.laddr addr, l.kaddr kaddr,  /* 1040651: Defn for v$lock */
                   s.ksusenum sid, r.ksqrsidt type, r.ksqrsid1 id1,
                   r.ksqrsid2 id2, l.lmode lmode, l.request request,
                   l.ctime ctime, l.block block
              from v$_lock l, x$ksuse s, x$ksqrs r
              where l.saddr = s.addr and l.raddr = r.addr and
                    s.inst_id = USERENV('Instance')) l, obj$ o, user$ u
      where l.id1 = o.obj#
      and   o.owner# = u.user#
      and   l.type = 'TM';
create or replace public synonym DBA_DML_LOCKS for DBA_DML_LOCKS;
grant select on DBA_DML_LOCKS to select_catalog_role;

/*
 * 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
 */

create or replace 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/Type', 2, 'Body',
           3, 'Trigger', 4, 'Index', 5, 'Cluster', 13, 'Java Source',
             14, 'Java Resource', 32, 'Java Data', to_char(ob.kglhdnsp)) 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;
create or replace public synonym DBA_DDL_LOCKS for DBA_DDL_LOCKS;
grant select on DBA_DDL_LOCKS to select_catalog_role;

/*
 * Show all the sessions waiting for locks and the session that holds the
 * lock.
 */
create or replace view DBA_WAITERS
(waiting_session
,holding_session
,lock_type
,mode_held
,mode_requested
,lock_id1
,lock_id2)
 as
select /*+ordered */ w.sid
      ,s.ksusenum
      ,decode(r.ksqrsidt,
                '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',
                r.ksqrsidt)
      ,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 (SSX)',  /* C */
                6, 'Exclusive',      /* X */
                l.lmode)
      ,decode(bitand(w.p1,65535),
                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(bitand(w.p1,65535)))
      ,r.ksqrsid1, r.ksqrsid2
  from v$session_wait w, x$ksqrs r, v$_lock l, x$ksuse s
 where w.wait_Time = 0
   and w.event like 'enq:%'
   and r.ksqrsid1 = w.p2
   and r.ksqrsid2 = w.p3
   and r.ksqrsidt = chr(bitand(p1,-16777216)/16777215)||
                   chr(bitand(p1,16711680)/65535)
   and l.block = 1
   and l.saddr = s.addr
   and l.raddr = r.addr
   and s.inst_id = userenv('Instance');

create or replace public synonym DBA_WAITERS for DBA_WAITERS;
grant select on DBA_WAITERS to select_catalog_role;

/*
 * Show all the sessions that have someone waiting on a lock they hold, but
 * that are not themselves waiting on a lock.
 */
create or replace view DBA_BLOCKERS as
select /*+ordered */ distinct s.ksusenum holding_session
  from v$session_wait w, x$ksqrs r, v$_lock l, x$ksuse s
 where w.wait_Time = 0
   and w.event like 'enq:%'
   and r.ksqrsid1 = w.p2
   and r.ksqrsid2 = w.p3
   and r.ksqrsidt = chr(bitand(p1,-16777216)/16777215)||
                   chr(bitand(p1,16711680)/65535)
   and l.block = 1
   and l.saddr = s.addr
   and l.raddr = r.addr
   and s.inst_id = userenv('Instance');

create or replace public synonym DBA_BLOCKERS for DBA_BLOCKERS;
grant select on DBA_BLOCKERS to select_catalog_role;


-- The End --

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/519536/viewspace-611759/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle ACE 总监,阿里云MVP,北京大学理学硕士,恩墨学院创始人,教育专家,中国区 Cloudera 首位官方授权大数据讲师,金牌培训专家,BDA大数据联盟创始人,OCM联盟创始人,ACCUG创始人、ACOUG核心专家,Blogger。

注册时间:2008-03-16

  • 博文量
    797
  • 访问量
    7997045