Miladin Modrakovic's Blog: Oraclue

Let say someone have an update,insert or delete statement that runs but has not been commited 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:

A user issues a COMMIT or ROLLBACK statement without a SAVEPOINT clause.
A user runs a DDL statement such as CREATE, DROP, RENAME, or ALTER. If the current transaction contains any DML statements,
Oracle first commits the transaction, and then runs and commits the DDL statement as a new, single statement transaction.
A user disconnects from Oracle. The current transaction is committed.
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  I 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 my script to show last statements for uncommitted transactions including sql text involved looks like :

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(+)