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

Answer 1

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

Answer 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).