Performance - Locks DDL

Show all ddl locks in the system

Slightly more simple version of the above

Generate kill statement for ddl locking sessions

Show all ddl locks in the system

select decode(lob.kglobtyp, 
  0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
  4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
  7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
  11, 'PACKAGE BODY', 12, 'TRIGGER',
  13, 'TYPE', 14, 'TYPE BODY',
  19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
  22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
  28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
  32, 'INDEXTYPE', 33, 'OPERATOR',
  34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
  40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
  42, 'MATERIALIZED VIEW',
  43, 'DIMENSION',
  44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
  48, 'CONSUMER GROUP',
  51, 'SUBSCRIPTION', 52, 'LOCATION',
  55, 'XML SCHEMA', 56, 'JAVA DATA',
  57, 'SECURITY PROFILE', 59, 'RULE',
  62, 'EVALUATION CONTEXT','UNDEFINED'
 ) object_type
, lob.kglnaobj object_name
, pn.kglpnmod lock_mode_held
, pn.kglpnreq lock_mode_requested
, ses.sid
, ses.serial#
, ses.username
from v$session_wait vsw
, x$kglob  lob
, x$kglpn  pn
, v$session ses
where vsw.event = 'library cache lock'
and vsw.p1raw = lob.kglhdadr
and lob.kglhdadr = pn.kglpnhdl
and pn.kglpnmod != 0
and pn.kglpnuse = ses.saddr
/

Slightly more simple version of the above

select ses.username
, ddl.session_id
, ses.serial#
, owner || '.' || ddl.name object
, ddl.type
, ddl.mode_held
from dba_ddl_locks ddl
, v$session ses
where owner like '%userid%'
and ddl.session_id = ses.sid
/