Find uncommitted transaction in Oracle

Find the uncommitted or incomplete transaction in Oracle

Query with V$transaction give result which transaction is not commited yet.

set lines 250
column start_time format a20
column sid format 999
column serial# format 999999
column username format a10
column status format a10
column schemaname format a10
select t.start_time,s.sid,s.serial#,s.username,s.status,s.schemaname,
s.osuser,s.process,s.machine,s.terminal,s.program,s.module,to_char(s.logon_time,'DD/MON/YY HH24:MI:SS') logon_time
from v$transaction t, v$session s
where s.saddr = t.ses_addr
order by start_time;

Find the SQL statement for uncommitted transaction in Oracle

SELECT S.SID, S.SERIAL#, S.USERNAME, S.OSUSER, S.PROGRAM, S.EVENT
  ,TO_CHAR(S.LOGON_TIME,'YYYY-MM-DD HH24:MI:SS') 
  ,TO_CHAR(T.START_DATE,'YYYY-MM-DD HH24:MI:SS') 
  ,S.LAST_CALL_ET, S.BLOCKING_SESSION, S.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 S, 
  V$TRANSACTION T 
WHERE S.SADDR = T.SES_ADDR;

Check your current session has uncommitted transaction if return one then it has 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;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.