How to find out uncommitted transactions in oracle 19c

 

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.