Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

How to rollback all active transactions on Oracle DB?

I execute query and see 4 transcations in ACTIVE status.

SELECT * 
FROM V$TRANSACTION
share|improve this question

migrated from stackoverflow.com Apr 27 '13 at 21:43

This question came from our site for professional and enthusiast programmers.

1  
Just kill their sessions :-) –  Egor Skriptunoff Apr 24 '13 at 11:15
    
the problem is that sessions are already killed, but transaction are still there –  user1545453 Apr 24 '13 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 '13 at 11:33

1 Answer 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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.