Oracle – Why Is Toad Asking Me To Commit/Rollback A SELECT With No Transaction?

Commit or rollback a SELECT, really?

Occasionally, you would be working with your favorite Oracle tool and when you are ready to close the tool, it will prompt you to either commit or rollback your work.

“What work?”, you wonder. The problem is, you have only done SELECT’s. Why is it prompting you to commit or rollback?

You know for a fact that you did not do any updates, but to confirm, you ask the DBA to look at the SQL history and there is nothing of any significance except that the DBA reports that there is an open transaction tied to your session and that your session is “ACTIVE”. This does not help answer the question though.

Why it is that way – the answer:

You ran a SELECT that involved a database link. Under this specific circumstance, as a distributed query, Oracle reserves an entry in the rollback segment on the query initiation side. The query itself will be visible on the remote side. Even though it is just a SELECT statement, it has to be accompanied by a commit or rollback on the initiation side for the open transaction to be closed. Generally, you can rollback if you know that you have not done anything.

How to check?

Irrespective of what you have done, if you are doubtful whether your session has an open transaction, you could run the below query:

SELECT dbms_transaction.local_transaction_id FROM dual;

The above query only works for the open session that you run it against. If the above query returns a non-null value, you do have an open transaction.

The lesson:

Queries (even just SELECT’s) over database links have to be finished by issuing a commit or rollback.

More to the lesson than what meets the eye:

If you thought that the remote session would now vanish at this point after the commit or rollback, you would have guessed wrong. In fact, the remote sessions (all sessions created in the remote databases by the different database link based queries) would not go away and they would be taking up valuable resources tied to them. They will only go away when the initiating session goes away, which could be several days later in some cases. However, there is a way to clean up the remote sessions. You do need ALTER SESSION privilege to do so.

Being clean

ALTER SESSION CLOSE DATABASE LINK <db_link_name>;

Ref/Further reading:

http://docs.oracle.com/cd/B28359_01/server.111/b28310/ds_appdev002.htm

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s