RE: Distributed transaction warning
- From: "Dunbar, Norman" <norman.dunbar@xxxxxxxxxxxxxxxxxxxxxxxxx>
- To: <Richard.Goulet@xxxxxxxxxxx>, "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 5 Feb 2010 15:33:55 -0000
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@some_link" and then immediately close the session:
----------------------------------
Timestamp: 15:31:13.828
select count(*) from user_users@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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
