Useful scripts to handle deadlock in Oracle

As a release engineer, when we do database integraiton, we might encounter Error: ORA-04020: deadlock detected while trying to lock… and Error: ORA-00060: deadlock detected while waiting for resource…
Below are some useful queries you can use to find out the deadlock and release it:
How to check db resource locking?

select c.owner, c.object_name, c.object_type, b.sid, 
b.serial#, b.status, b.osuser, b.machine, b.PROCESS
from v$locked_object a ,
v$session b,
dba_objects c
where b.sid = a.session_id
and a.object_id = c.object_id;

How to check db session status?

set linesize 80;
set pagesize 60;
set newpage 0;
set lines 1000;
select substr(a.spid,1,9) pid, substr(b.sid,1,5) sid, substr(b.machine,1,6) box, 
substr(b.username,1,10) username, substr(b.osuser,1,8) os_user, 
substr(b.program,1,30) program,status
from v$session b, v$process a where b.paddr = a.addr 
and type='USER' order by spid;

How to check what the session is waiting?

select wait_class, event, sid, state, wait_time, seconds_in_wait
from v$session_wait
where sid=<provide the waiting sid you get from above query>
order by wait_class, event, sid
/

select BLOCKING_SESSION_STATUS, BLOCKING_SESSION
from v$session
where sid = <provide the waiting sid you get from above query>

select * from v$session_wait_class where sid =

<provide the waiting sid you get from above query>;

select substr(b.machine,1,6) box, substr(b.username,1,10) username, 
substr(b.osuser,1,8) os_user, substr(b.program,1,30) program,status
from v$session b, v$process a where b.paddr = a.addr and type='USER' 
and b.program like '%yourprogramname%' order by spid;

select sid, serial#, username
from v$session where sid in
(select blocking_session from v$session)

You can also use the alert logs to trace the error:
alert logs:
$ORACLE_HOME/admin/../diag/rdbms/$ORACLE_ID/$ORACLE_ID/trace
After that, you can use below alter statement to kill the session:
alter system kill session ‘sid, serial#’ immediate;