Friday, June 13, 2014

Find Oracle apparent deallocks

Your application is blocked? Perhaps it is due to deadlock on database objects. So, if you have an Oracke DB Server, you can verify this with these few requests I found on the web :

  • List blocked sessions  :  select sid, serial#, username, command, lockwait, osuser from v$session where lockwait is not null
  • Find SQL requests concerned : 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
  • Kill concerned session (you need to have dba priviledges) : alter system kill session 'sid, serial#';