Siebel SQLs/Error Messages >  Deadlock and blocking locks in Oracle

 

Blocking lock occurs when a user/sql tries to update data. This can lock a database for a long time
We can kill the user to resolve the lock

 

SELECT LPAD(' ',DECODE(l.xidusn,0,3,0)) || l.oracle_username "User Name",
o.owner, o.object_name, o.object_type
FROM v$locked_object l, dba_objects o
WHERE l.object_id = o.object_id
ORDER BY o.object_id, 1 desc;

 


We can Find and kill the users in oracle/resolve deadlocks
like this

 

select sid, serial# from v$session where username = 'USER';
alter system kill session 'SID,SERIAL#';

 

e.g.
select sid, serial# from v$session where username = 'PAVEL';
alter system kill session '4869,40384';
commit;

 

Deadlocks occur when more than one resource tries to update the same data
ORA-00060: deadlock detected while waiting for resource