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′);
commit;
update xxlocks_test set name = name || ‘.1′ where id = 1;
Session #2:
update xxlocks_test set name = name || ‘.2′ where id = 1;
Session #3:
@locks
sql4oracle – locks.sql
————————————————————–
rev. 28-mar-2010 / Thomas Lundqvist
wait
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
Download: sql4oracle.zip - all sql4oracle scripts - posted 14-03-2013