developerWorks® ibm.com/developerWorks/

How to check Oracle database for long running transactions

Long running transactions are those transactions which has startedsome time ago but have not yet committed or rollback. Oracle provides dynamic performance views to find out the database user session detail who have started these long running transactions.

Overview

When a database user initiates a transaction against an Oracle database and left it open for whatever reason for a long time, it is considered a long running transaction. Oracle will insert an entry in v$transaction for each active transaction in the database. When the transaction is completed either using commit or rollback, the row from v$transaction will disappear. There might be many such long running transactions. This articles showshow to find out which is the earliest or oldest among these many long running transactions.

Prerequisites

To follow this article, basic knowledge about the following is required.

  • Oracle SQLPLUS
  • Oracle dynamic performance views V$session, V$transaction, V$locked_object, dba_objects

Usage and example of long running transaction

The below query returns the oldest of all long running transactions with the session details (sid,serial#) of the session who has initiated the transaction along with start SCN of that transaction.

Select a.sid c1 ,a.serial# c2 , min(start_scn) c3 from gv$session a, gv$transaction b where a.taddr = b.addr

group by a.sid,a.serial#

SID SERIAL# MIN(START_SCN)

------

225 51915 521933476

Also, it is important to find out the name of the tables/objects involved in this transaction and database username, sid, serial# of session who initiated this transaction, owner of the object and date/time until when the transactions have started.

SELECT t.start_scnb c1 ,t.start_time c2 ,s.username c3, o.object_name c4 , o.owner c5 , s.sid c6, s.serial# c7 FROM gv$transaction t, gv$session s, gv$locked_object l, dba_objects o

WHERE t.ses_addr = s.saddr AND t.xidusn = l.xidusn AND t.xidslot = l.xidslot AND t.xidsqn = l.xidsqn AND l.object_id = o.object_id

START_SCNB START_TIME USERNAME OBJECT_NAME OWNER SID SERIAL

------

521933476 05/25/15 11:17:38 NIR1133 TT12 NIR1133 225 51915

521933476 05/25/15 11:17:38 NIR1133 TT34 NIR1133 225 51915

Resources

Learn

  • Learn more about how IBM InfoSphere Change Data Capture integrates information across heterogeneous data stores in real time.

Discuss

  • Participate in the discussion forum.
  • Get involved in the CDC (Change Data Capture) community. Connect with other CDC users while exploring the developer-driven blogs, forums, groups, and wikis.
  • Get involved in the My developerWorks community. Connect with other developerWorks users while exploring the developer-driven blogs, forums, groups, and wikis.

© Copyright IBM Corporation 2015 Trademarks

Infosphere Data Replicationcomparison with Apache Sqoop Incremental Import