Shows all locks. Copy paste the “alter system…” to kill a session.

The script will create these two global temporary tables:

create global temporary table xx_dba_locks as select * from dba_locks where 1=2;
create global temporary table xx_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));

To test the locks.sql, you can use these instructions: 

Then you need 3 sqlplus sessions:

Session #1:

create table xxlocks_test (id number, name varchar2(30));

insert into xxlocks_test values(1,’1′);
insert into xxlocks_test values(2,’2′);


update xxlocks_test set name = name || ‘.1’ where id = 1;

Session #2:

update xxlocks_test set name = name || ‘.2’ where id = 1;

Session #3:

sql4oracle – locks.sql
rev. 28-mar-2010 / Thomas Lundqvist

session Description                      Kill
——- ——————————– —————————————–
1076    None                             alter system kill session ‘1076,58217’;
-946    Transaction Exclusive Exclusive  alter system kill session ‘946,52881’;

Now you have a real deadlock situation – session #1 does not have a clue whats going on – everything is normal. Session #2 is deadlocked – unable to do anything. Session #3 is running the @locks script and can see the two sessions. To resolve the deadlock, either session #1 must do commit or rollback, or session #3 can  do on of the two proposed “alter kill session”

Syntax: @locks

find objcets - @obj (0 downloads)

Leave a Reply