FAQ
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."

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??

Dick Goulet
Senior Oracle DBA/NA Team Lead
PAREXEL International
900 Chelmsford St, Suite 310
Lowell, MA 01821
978.614.2857
Richard.Goulet_at_PAREXEL.COM

The information transmitted in this communication is intended only for
the person or entity to which it is addressed and may contain
confidential and/or privileged material. Any review, retransmission,
dissemination or other use of, or taking of any action in reliance upon,
this information by persons or entities other than the intended
recipient is prohibited. If you received this in error, please destroy
any copies, contact the sender and delete the material from any
computer.

Search Discussions

  • Joel Slowik at Feb 5, 2010 at 2:46 pm
    Is toad setup to run a script at startup or the like?



    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Goulet, Richard
    Sent: Friday, February 05, 2010 9:44 AM
    To: ORACLE-L
    Subject: Distributed transaction warning



    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."

    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??

    Dick Goulet
    Senior Oracle DBA/NA Team Lead
    PAREXEL International
    900 Chelmsford St, Suite 310
    Lowell, MA 01821
    978.614.2857
    Richard.Goulet_at_PAREXEL.COM

    The information transmitted in this communication is intended only for
    the person or entity to which it is addressed and may contain
    confidential and/or privileged material. Any review, retransmission,
    dissemination or other use of, or taking of any action in reliance upon,
    this information by persons or entities other than the intended
    recipient is prohibited. If you received this in error, please destroy
    any copies, contact the sender and delete the material from any
    computer.



    Confidentiality Note: This electronic message transmission is intended only for the person or entity to which it is addressed and may contain information that is privileged, confidential or otherwise protected from disclosure. If you have received this transmission, but are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or use of the contents of this information is strictly prohibited. If you have received this e-mail in error, please contact Continuum Performance Systems at {203.245.5000} and delete and destroy the original message and all copies.
  • Vit\.spinka at Feb 5, 2010 at 3:31 pm
    This happens whenever she looks at a view that is composed entirely of data from a second database.
    This is expected behavior and is not TOAD-specific. Any distributed select (i.e. over a db link) in Oracle starts a transaction.

    Vit
  • Dunbar, Norman at Feb 5, 2010 at 3:33 pm
    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 =

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedFeb 5, '10 at 2:43p
activeFeb 5, '10 at 3:33p
posts4
users4
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase