0

How to rollback all active transactions on Oracle DB?

I execute query and see 4 transcations in ACTIVE status.

SELECT * 
FROM V$TRANSACTION
3
  • 1
    Just kill their sessions :-)
    – Egor Skriptunoff
    Apr 24, 2013 at 11:15
  • the problem is that sessions are already killed, but transaction are still there
    – user1545453
    Apr 24, 2013 at 11:18
  • 6
    @user1545453 if the session is killed, then the transaction is either distributed one or is being rolled back. It often happens that some load(or update) lasts very long, therefore it is killed. Then you have to wait for approx. same time till the transaction is rolled back. Check the column used_urec in v$transaction and you will see whether it is rolling back or forward.
    – ibre5041
    Apr 24, 2013 at 11:33

1 Answer 1

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.