Thursday, January 1, 2009

How to check any deadlock and other locking issues

Normally deadlock generates dump file and automatically is released by oracle system process
1) check v$session
sqlplus> select sid, serial#, username, command, lockwait, osuser from v$session where lockwait is not null

2) To kill a locked session, first need to find sid, serial and use
sqlplus>alter system kill session 'sid, serial#';
*** you need have dba priviledge to kill sessions

3. To find which SQL has lock wait
sqlplus>select sql_text from v$sqltext where (address,hash_value) in (select sql_address,sql_hash_value from v$session where lockwait is not null) order by address, hash_value, piece

3 comments: