7 Replies Latest reply: Jan 27, 2009 11:26 PM by 247514 RSS

    DBA commit other session's transaction?

    alexlizn
      Hi, everyone,
      A quick question, can a DBA commit {color:#0000ff}other session's transaction{color}?

      If so, how?
      Thanks!
      Alex
        • 1. Re: DBA commit other session's transaction?
          user097815
          From what i know, its not possible. As a dba can monitor a session, but cant commit the changes. It only the user who open the session can commit that changes. Again its just my thinking. But there are experts here and ppl on here with lots and lots of knowledge can answer ur question. But from what i know its not possible.
          • 2. Re: DBA commit other session's transaction?
            JustinCave
            A DBA can commit or roll-back in-doubt distributed transactions initiated by some other sessions. Assuming that we are not talking about distributed transactions, though, a DBA cannot commit another session's transaction. The DBA could kill the other session, which as a side effect would roll back that session's transaction.

            Justin
            • 3. Re: DBA commit other session's transaction?
              alexlizn
              Hi, there,
              Thanks for your input!

              Actually, in a distributed database system, DBAs can use COMMIT FORCE to manually commit an in-doubt distributed transaction. Check this out:

              Link: [http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_4010.htm#SQLRF01110]

              However, I don't think we use a distributed database system here. For one thing, a query into data dictionary view DBA_2PC_PENDING returned nothing.

              I'm still looking....
              Alex
              • 4. Re: DBA commit other session's transaction?
                alexlizn
                Justin,
                Thank you for your answer!

                Times and times again, it is helpful!

                I was aware of the distributed-transaction-commit. However after spending one hour updating some ad hoc records in Toad before it went hanging, I just hope this is something I didn't know that could save me from manually updating those records all over again.

                I think I should also look beyond TOAD for another better tool.
                Alex
                • 5. Re: DBA commit other session's transaction?
                  247514
                  alexlizn wrote:
                  Justin,
                  Thank you for your answer!

                  Times and times again, it is helpful!

                  I was aware of the distributed-transaction-commit. However after spending one hour updating some ad hoc records in Toad before it went hanging, I just hope this is something I didn't know that could save me from manually updating those records all over again.

                  I think I should also look beyond TOAD for another better tool.
                  Alex
                  If you had been updating your table one hour without commit that's something you should avoid. Having long transaction running on a busy database can cause more problem. If all these changes you made are not logically one transaction (either all done or nothing done) you should commit intermediately to save the changes you made.

                  It's not necessarily a toad problem, should you have used other tools if you made many changes without commit for an hour you might encounter the same problem.
                  • 6. Re: DBA commit other session's transaction?
                    alexlizn
                    Hi, yingkuan,
                    I disagree.

                    Transactions can be as long as what the user logic demands. There is no transactions that is too long or too large. Oracle doesn't break, slowdown, or underperform because of long or big transactions. If you look deep into how oracle handles transactions in the background, you'll see why.

                    When I mentioned TOAD problem, I meant to say that tool simply shuts down itself, or hangs forever. It's extremly annoying for people like you and me, fellow developers. I don't just mean losing some source code.

                    just to clarify.
                    btw, Thanks for your reply! I already maked it answered.
                    Alex
                    • 7. Re: DBA commit other session's transaction?
                      247514
                      Transactions can be as long as what the user logic demands. There is no transactions that is too long or too large. Oracle doesn't break, slowdown, or underperform because of long or big transactions. If you look deep into how oracle handles transactions in the background, you'll see why.
                      It's interesting you mentioned that. I hope you really looked deep into how oracle handles transactions so you can en-light us how long and large transaction not affecting performance.

                      perhaps we should start with Oracle Concept shall we?
                      http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/transact.htm#CNCPT117

                      In a nutshell long and large transaction has potential performance impact at least in these aspects.

                      A transaction will generate undo information, until the transaction is committed, these undo segment can't be reused. As transaction grows the undo space held hostage grows. Potential performance impact No.1

                      A transaction usually hold locks on rows you made changes. until your transaction is committed, no other user can update these rows and need to wait for the lock. Potential performance impact No.2

                      The third one is situation like yours, when a session held long and big transaction dropped, Oracle need to rollback all the changes made to database. Some of these changes already been flushed to disk (especially since it's long and large transaction). In that case, Oracle will need to load all changed data blocks and apply undo information back.

                      On the other hand, since you are seeking ways for DBA to commit the uncommit change you made recklessly, that means you could have committed the changes. There's certainly no user logic to hold these changes as one single transaction.