How to check if current SQL session has uncommited changes

oracleoracle-sql-developersession

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.

Best Answer

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.

Related Question