Uncommitted transactions in Oracle

 Lets assume that we have an update, insert or delete statement that running but has not been committed yet. So question is how to show uncommitted transactions ?

First some background about transaction management in Oracle..

A transaction is a logical unit of work that contains one or more SQL statements.

A transaction ends when any of the following occurs:

1. A user issues a COMMIT or ROLLBACK statement without a SAVEPOINT clause.
2. A user runs a DDL statement such as CREATE, DROP, RENAME, or ALTER. If the current transaction contains any DML statements,
3. Oracle first commits the transaction, and then runs and commits the DDL statement as a new, single statement transaction.
4. A user disconnects from Oracle. The current transaction is committed.
5. A user process terminates abnormally. The current transaction is rolled back.

When a transaction is committed, the following occurs:

1. The internal transaction table for the associated undo tablespace that the transaction has committed, and the corresponding unique system change number (SCN) of the transaction is assigned and recorded in the table.

This can be seen using v$transaction view. To get more  info we normally join v$transaction and v$session views.

e.g.

select t.start_time,s.sid,s.serial#,s.username,s.status  from v$transaction t, v$session s

where s.saddr = t.ses_addr ;

2. The log writer process (LGWR) writes redo log entries in the SGA’s redo log buffers to the redo log file. It also writes the transaction’s SCN to the redo log file.

3. Oracle releases locks held on rows and tables ( this info is visible using v$locked_object view ) and marks transaction complete.

This info is visible using v$locked_object view.

And below is the script to find the last statements for uncommitted transactions including sql text involved with those sessions :

select b.inst_id, b.sid, b.serial#,b.username,b.machine ,b.status,b.prev_sql_id,c.sql_text,d.object_id,e.object_name,

a.start_time,to_char(b.logon_time,'MM/DD/YY HH24:MI:SS') logon_time

from    gv$transaction a ,

gv$session b ,

gv$sql c,

v$locked_object d,

all_objects e

where a.inst_id = b.inst_id

and a.ses_addr = b.SADDR

and b.prev_sql_addr = c.address(+)

and b.prev_hash_value = c.hash_value(+)

and b.prev_child_number = c.child_number(+)

and b.inst_id = c.inst_id(+)

and b.prev_sql_id=c.sql_id

and d.object_id=e.object_id

and d.session_id=b.sid(+) ;