How can I tell if I have uncommitted work in an Oracle transaction?

Question

Is there a way to tell if I have uncommitted work (ie DML) in a transaction? Maybe a data-dictionary view I can query?

A method to find this out both from within and outside of the session running the open transaction would be welcome.

Thank you

Solution 1

SELECT  *
FROM    v$session v
WHERE   v.AUDSID = userenv('sessionid')
    AND v.TADDR IS NOT NULL

Solution 2

If you don't have access to v$session you can use

select dbms_transaction.local_transaction_id from dual;

This only works from within the session but doesn't need v$ privileges. If it returns a non-null, you have started a transaction. That normally means uncommitted changes, but there are exceptions. If you issued a savepoint, changed data and rolled back to the savepoint, the transaction still 'lives'. Also, using database links starts transactions, even just for selects (or they used to).

Comments

[Gaurav Soni]

:How can i see if there is any uncommitted work in other session .I have done update in two different session ,but i am seeing only one record in each session .I am expecting two records from both the session .Is it possuble to see other session uncommited data.

[Quassnoi]

@GauravSoni: please post it as a question.

[David Baucum]

This. Solutions that rely on enhanced privileges that many database users are unlikely to have are, imo, not good solutions.