6 Replies Latest reply: Dec 31, 2012 1:28 PM by John Spencer RSS

    Commit required after select over Dblink

    976379
      Hi,
      whenever i am trying to fire select query over a dblink, even though the transaction is complete and all rows are fetched, i am being asked for commit/rollback by the tool. I need to know that this is fine for any tool, but what will happen to the session if the same is being initiated by a view.

      i mean, i am selecting from a view but the view is internally firing a query over dblink, in that case do the session will remain open or it will be closed by oracle?

      Thanks.
        • 1. Re: Commit required after select over Dblink
          Manik
          Hi,

          Got this info from google and thought to share.

          When you select through a DB Link, Oracle considers that to be the start of the transaction. So, when you end your session in your DB Tool, it asks Oracle “are there any transactions”. If you’ve done a select through a DB Link, Oracle says “yes”, so your DB Tool asks if you want to commit or rollback.

          This happens with Toad often.

          Cheers,
          Manik.
          • 2. Re: Commit required after select over Dblink
            976379
            Hi,
            Thanks for the details,
            But my confusion is, if i am using this view in some automated process , then will it requires to fire a commit/rollback or oracle will close the session by itself.

            Because, once we configure this , that view will be used by application and end users who are not aware of this details.

            Thanks.
            • 3. Re: Commit required after select over Dblink
              hitgon
              http://docs.oracle.com/cd/B28359_01/server.111/b28310/ds_appdev002.htm
              • 4. Re: Commit required after select over Dblink
                Manik
                Try looking at this metadata view:

                V$DBLINK is the view which has data about the dblink transactions.

                Try setting session time out for destination session (Database link session). Thus your prob can be solved.

                Cheers,
                Manik.
                • 5. Re: Commit required after select over Dblink
                  rp0428
                  >
                  But my confusion is, if i am using this view in some automated process , then will it requires to fire a commit/rollback or oracle will close the session by itself.

                  Because, once we configure this , that view will be used by application and end users who are not aware of this details.
                  >
                  A COMMIT will commit any transaction that the view is part of. So if that view is used as part of a larger transaction that is not yet complete the 'hidden' commit can cause some serious data integrity issues.
                  • 6. Re: Commit required after select over Dblink
                    John Spencer
                    You only need the commit or rollback if you want to close the database link. As others have said, Oracle automatically starts a transaction when you select over a database link, and that transaction needs to be completed at some point. The "natural" commit at the end of your transaction will do fine for ending the database link transaction.

                    Many tools will "see" that you have an open transaction when you try to exit the tool (or close the particular session) and will prompt you for a commit or rollback to end the transaction.

                    john