Oracle transactions deadlock

deadlockoracle

How to rollback all active transactions on Oracle DB?

I execute query and see 4 transcations in ACTIVE status.

SELECT * 
FROM V$TRANSACTION

Best Answer

Deadlock (ORA-00060) are automatically resolved by Oracle and signalled in the alter.log. Transactional locks have to solved manually. You can kill session from sql command line using:

SQL> alter system kill session 'sid, serial#';

This will mark session for being killed whener it's possible. Sometime kill is not immediate. IF you want and immediate kill you can, on a unix system, run

kill -9 processid

This will kill immediatly the session forcing the PMON to rollback any uncommitted data. To retrieve processid (SPID) run:

SQL> SELECT spid
FROM v$process
WHERE NOT EXISTS ( SELECT 1 FROM v$session WHERE paddr = addr);

A final note: in windows process is slightly different. To kill a session you have to run:

C:\> orakill <SID> <spid> 

where <SID>  = the Oracle instance name (ORACLE_SID)
      <spid> = the thread id of the thread to kill
Related Question