How to check if current SQL session has uncommited changes?

Question

Say you are in SQL Developer and want to check if the current user session (i.e. your connection) has uncommitted changes.

If you quit SQL Developer, it does such a check and displays a dialog box how to proceed. I want to check it without exiting - e.g. via executing a special SQL statement or a SQL Developer action.

Ideally, it would be great if one could configure SQL Developer such that it is visually indicated if the current session is 'dirty' (i.e. has uncommited changes) - e.g. via displaying a red margin around the worksheet.

Answer 1

There is also

SELECT dbms_transaction.local_transaction_id FROM dual;

which will show a transaction id if there are pending commits.

Answer 2

You could check V$TRANSACTION. Here is an example:

create table t(a number);

Table created.

insert into t values (1);

1 row created.

select t.status
from v$transaction t
join v$session s
on t.ses_addr = s.saddr
where s.sid = sys_context('USERENV', 'SID');

STATUS
----------------
ACTIVE

After commiting:

commit;

Commit complete.

select t.status
from v$transaction t
join v$session s
on t.ses_addr = s.saddr
where s.sid = sys_context('USERENV', 'SID');

no rows selected

For this you have to grant SELECT privilege on SYS.V_$TRANSACTION and SYS.V_$SESSION though.