Ameer Khan

V$TRANSACTION lists the active transactions in the database

Use following query to find out uncommitted active transactions in database

select t.start_time,a.sid,a.serial#,a.username,a.status,a.schemaname,

a.osuser,a.process,a.machine,a.terminal,a.program,a.module,to_char(a.logon_time,’DD/MON/YY HH24:MI:SS’) logon_time

from v$transaction t, v$session a

where a.saddr = t.ses_addr

order by start_time;

To find out sql statement of uncommitted transaction

SELECT a.SID, a.SERIAL#, a.USERNAME, a.OSUSER, a.PROGRAM, a.EVENT

  ,TO_CHAR(a.LOGON_TIME,’YYYY-MM-DD HH24:MI:SS’)

  ,TO_CHAR(T.START_DATE,’YYYY-MM-DD HH24:MI:SS’)

  ,a.LAST_CALL_ET, a.BLOCKING_SESSION, a.STATUS

  ,(

    SELECT Q.SQL_TEXT

    FROM V$SQL Q

    WHERE Q.LAST_ACTIVE_TIME=T.START_DATE

    AND ROWNUM<=1) AS SQL_TEXT

FROM V$SESSION a,

  V$TRANSACTION T

WHERE a.SADDR = T.SES_ADDR;

To find out if your current session has any uncommitted transaction

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;

If the output is zero it means it has no uncommitted transaction.

If output is 1, it states session has uncommitted transactions.