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