Re: Oracle 7.3 LOCK Script

From: Gerd Kock <info_at_gerd-kock.de>
Date: 2000/05/02
Message-ID: <390f346a.8841035@news.btx.dtag.de>#1/1

On Tue, 2 May 2000 10:23:00 -0500, "Jose A. Silva" <jsilvae_at_yahoo.com> wrote:

>Does anyone have a script to show the current lockings in the database, and
>who is locking who ?
>Thanks.
>
>

this script works for 7.x databases:

drop table lock_holders;
create table LOCK_HOLDERS
(
  waiting_session number,
  holding_session number,

  lock_type         varchar2(17),
  mode_held         varchar2(10),
  mode_requested    varchar2(10),
  lock_id1          varchar2(10),
  lock_id2          varchar2(10)

);

drop   table dba_locks_temp;
create table dba_locks_temp as select * from sys.dba_lock;
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.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;

select lpad(' ',3*(level-1)) || waiting_session waiting_session,

	lock_type,
	mode_requested,
	mode_held,
	lock_id1,
	lock_id2

 from lock_holders
connect by prior waiting_session = holding_session   start with holding_session is null;

select l.holding_session, s.serial#, s.username, s.osuser from lock_holders l,v$session s
where not holding_session is null
and s.sid = l.holding_session;

select 'alter system kill session
'''||l.holding_session||','||s.serial#||''';' from lock_holders l,v$session s
where not holding_session is null
and s.sid = l.holding_session;
drop table lock_holders; Received on Tue May 02 2000 - 00:00:00 CDT