Tranactions

Identify incomplete transactions in DB

You can check if your session has a row in V$TRANSACTION:


SQL> SELECT COUNT(*) 

       FROM v$transaction t, v$session s, v$mystat m 

      WHERE t.ses_addr = s.saddr 

        AND s.sid = m.sid 

        AND ROWNUM = 1; 



  COUNT(*) 

---------- 

         0 



SQL> insert into a values (1); 



1 row inserted 



SQL> SELECT COUNT(*) 

       FROM v$transaction t, v$session s, v$mystat m 

      WHERE t.ses_addr = s.saddr 

        AND s.sid = m.sid 

        AND ROWNUM = 1; 



  COUNT(*) 

---------- 

         1 



SQL> commit; 



Commit complete 



SQL> SELECT COUNT(*) 

       FROM v$transaction t, v$session s, v$mystat m 

      WHERE t.ses_addr = s.saddr 

        AND s.sid = m.sid 

        AND ROWNUM = 1; 



  COUNT(*) 

---------- 

         0

A transaction pending, not if you have uncommitted data.


select s.sid 

      ,s.serial# 

      ,s.username 

      ,s.machine 

      ,s.status 

      ,s.lockwait 

      ,t.used_ublk 

      ,t.used_urec 

      ,t.start_time 

from v$transaction t 

inner join v$session s on t.addr = s.taddr;

Use can use the following SQL to find inactive transactions to kill after with ALTER SYSTEM KILL SESSION 'sid,serial#';


SELECT * FROM V$TRANSACTION WHERE STATUS='ACTIVE';