Enter Your Email

Enter your email address:

Delivered by FeedBurner

Custom Search

Tuesday, January 24, 2006

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

No comments:

Post a Comment

Copyright © 2005 - 2008 DBA-ONWeb Technical blog. All rights reserved