SQL*Plus command file to log transaction locks.

oracle_lock_mon.sql

Rem oracle_lock_mon.sql
Rem
Rem    NAME
Rem      oracle_lock_mon.sql
Rem
Rem    DESCRIPTION
Rem	     SQL*Plus command file to log transaction locks.
Rem      This script is based on the
Rem      /rdbms/admin/utllockt.sql script.
Rem
Rem    NOTES
Rem      Execute the script as the SYSTEM or SYS user.
Rem
Rem    REQUIREMENTS
Rem      Prior to executing this script, run catblock.sql as it creates
Rem      the lock views that oracle_lock_mon.sql needs.
Rem
Rem        sqlplus / as sysdba
Rem        @?/rdbms/admin/catblock.sql

drop table lock_holders;

create table LOCK_HOLDERS
(
  waiting_session number,
  holding_session number,
  lock_type varchar2(26),
  mode_held varchar2(14),
  mode_requested varchar2(14),
  lock_id1 varchar2(22),
  lock_id2 varchar2(22)
);

drop table dba_locks_temp;
create table dba_locks_temp as select * from dba_locks;

insert into lock_holders
select
  w.session_id,
  h.session_id,
  w.lock_type,
  h.mode_held,
  w.mode_requested,
  w.lock_id1,
  w.lock_id2
from
  dba_locks_temp w,
  dba_locks_temp 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;

commit;

drop table dba_locks_temp;

insert into lock_holders
select
  holding_session,
  null,
  'None',
  null,
  null,
  null,
  null
from
  lock_holders
minus
select
  waiting_session,
  null,
  'None',
  null,
  null,
  null,
  null
from
  lock_holders;

commit;

set linesize 400;
set pagesize 40;

column waiting_session format a17;
column lock_type format a26;
column mode_requested format a14;
column mode_held format a14;
column lock_id1 format a22;
column lock_id2 format a22;
column username format a30;
column status format a8;
column server format a9;
column osuser format a30;
column machine format a30;
column program format a30;
column object format a30;
column row_wait_row# format a20;
column sql_text for a80 wrap;

spool oracle_lock_mon.out append

set heading off;

select
  '*** ' || to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') || ' ***'
from
  dual;

set heading on;

select
  lpad(' ',3*(level-1)) || waiting_session waiting_session,
  lock_type,
  mode_requested,
  mode_held,
  lock_id1,
  lock_id2,
  (select distinct(username) from v$session where sid=lock_holders.waiting_session) username,
  (select distinct(status) from v$session where sid=lock_holders.waiting_session) status,
  (select distinct(server) from v$session where sid=lock_holders.waiting_session) server,
  (select distinct(osuser) from v$session where sid=lock_holders.waiting_session) osuser,
  (select distinct(machine) from v$session where sid=lock_holders.waiting_session) machine,
  (select distinct(program) from v$session where sid=lock_holders.waiting_session) program,
  (select distinct(name) from sys.obj$ where obj# = (select distinct(row_wait_obj#) from v$session where sid=lock_holders.waiting_session)) object,
  (select distinct dbms_rowid.rowid_create(1,row_wait_obj#,row_wait_file#,row_wait_block#,row_wait_row#) from v$session where sid=lock_holders.waiting_session and row_wait_obj# <> -1) row_wait_row#,
  (select distinct(sql_text) from v$sql where hash_value = (select sql_hash_value from v$session where sid=lock_holders.waiting_session)) sql_text
from
  lock_holders
connect by prior
  waiting_session = holding_session
start with
  holding_session is null;

spool off;

drop table lock_holders;

quit;