Home > Troubleshooting > Uncommitted transactions

Uncommitted transactions

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



About these ads
Categories: Troubleshooting
  1. October 21, 2009 at 8:28 am

    Oracle first commits the transaction, and then runs and commits the DDL statement as a new, single statement transaction.
    This is very true. As a consequence, even an unsuccessful DDL commits the transaction.

    ALTER most alter’s commands, but not alter system and alter session.

    also ANALYZE do commit.

    In one interview I were asked : Could you see uncommited transaction in another session.

    Of course you can ;)
    You need to suspend your transaction in the first session and resume it your second session by using the DBMS_XA packages !!!

  2. matjazc
    October 21, 2009 at 9:06 am

    Laurent, did they hire you, or were they replaced by you :)

  3. October 21, 2009 at 2:06 pm

    :) I am too much hooked on SQL questions !!!

  4. October 21, 2009 at 3:08 pm

    Hi Laurent,

    Good comment.

    Did you pass that interview ?

    Miladin :)

  5. October 27, 2009 at 8:04 am

    Indeed :)

  6. Deepak
    July 16, 2010 at 4:00 pm

    I would like to subscribe your blog.

  1. No trackbacks yet.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 46 other followers

%d bloggers like this: