RE: Distributed transaction warning
Date: Fri, 5 Feb 2010 15:33:55 -0000
Message-ID: <919FC80F27E0C6428106496EDF92A7520B4253F5_at_EXCCLUS05.PRODDS.NTNL>
Hi Richard,
>> OK, all of you TOAD users/guru's out there, I've a user who
>> is getting the following error message from TOAD that I
>> can't pin down;
>>
>> "Auto Commit is disabled. A session that has pending
>> transactions is about to close."
Sounds like a query over a database link.
>> This happens whenever she looks at a view that is composed
>> entirely of data from a second database. To make matters
>> even more complicated the database that she's connected to
>> is Oracle 10g R1 and the source db is 8.1.7. Now all she is
>> doing is reading the data, no updated, deletes, even
>> cut/paste not being used. Anyone got any idea about what is
>> going on??
If I remember correctly, when you query a remote database over a link, a transaction is started for you at the remote end. It usually manifests itself here when I have (too) many sessions open down a link and I get the "too many database links open" error.
SQL*Plus simply lets you exit without terminating the remote transaction but Toad, being polite, lets you know that you have responsibilities to the remote database and you should, possibly do something.
I get this message all the time, I simply commit/rollback as I feel at the time. It makes no difference if the query was completely read only, you still have a transaction logged.
Just commit or rollback - either way, it's not going to cause any problems. I'd tend to rollback myself, just in case!
This is what Toad's sql Monitor shows when I "select count(*) from user_users_at_some_link" and then immediately close the session:
Timestamp: 15:31:13.828
select count(*) from user_users_at_some_link
Timestamp: 15:31:34.734
begin :id := sys.dbms_transaction.local_transaction_id; end; i = '3'
Timestamp: 15:31:44.531
Successful rollback.
Timestamp: 15:31:44.562
select 'x' from dual
Timestamp: 15:31:44.562
begin :id := sys.dbms_transaction.local_transaction_id; end; i = <NULL>
Timestamp: 15:31:44.578
Successful rollback.
Timestamp: 15:31:45.328
Successful rollback.
Timestamp: 15:31:45.359
Logoff (session tag: 0xC9118D0).
Hope that helps.
Cheers,
Norm. [TeamT] <-- Toad "guru" allegedly! ;-)
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.
We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.
If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Feb 05 2010 - 09:33:55 CST