2

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.

4

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.

  • With one caveat. Select from @dblink would generate a dummy transaction with a bit of undo. It can be committed or rolled back without any impact. – kubanczyk Aug 15 '14 at 9:34
  • Hm, I am wondering how the Oracle SQL Developer program implement its uncommitted-transaction-check (while exiting)? I mean, it does not need extra privileges. – maxschlepzig Aug 23 '14 at 8:31
  • This will not work for transactions started with SET TRANSACTION READ WRITE NAME 'tran1'; – Just a learner Aug 5 at 11:04
4

There is also

SELECT dbms_transaction.local_transaction_id FROM dual;

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.