9 Replies Latest reply on Dec 25, 2009 6:24 PM by MichaelS

    commit on remote database?

    vis1985
      Can we fire commit on remote database?
        • 1. Re: commit on remote database?
          Hoek
          removed wrong reply

          Edited by: hoek on Dec 25, 2009 1:29 PM
          • 2. Re: commit on remote database?
            730428
            Do you mean something like:
            Update tab@link set col='val';
            commit;
            Yes, you can.

            Max
            [My Italian Oracle blog|http://oracleitalia.wordpress.com]
            • 3. Re: commit on remote database?
              Lubiez Jean-Valentin
              Hello,


              From a Client you can connect to a remote database, execute a transaction and commit it.

              Else in a distributed database, you can have a transaction executed across several databases and the
              commit process uses the *2 phase commit* mechanism.

              You'll find here more information about distributed transaction:

              [http://download.oracle.com/docs/cd/E11882_01/server.112/e10595/ds_concepts004.htm#i1008726]

              Hope it can help.
              Best regards,
              Jean-Valentin
              • 4. Re: commit on remote database?
                730428
                What do you mean Hoek?
                SQL> delete t@mylink;
                
                Eliminata 1 riga.
                
                SQL> commit;
                
                Commit completato.
                Max
                [My Italian Oracle blog|http://oracleitalia.wordpress.com]
                • 5. Re: commit on remote database?
                  Hoek
                  You're right, I just found out
                  Ran into an [ORA-02064|http://download.oracle.com/docs/cd/E11882_01/server.112/e10880/e1500.htm#sthref1231] , but that has nothing to do with OP's question.
                  Thanks for pointing out.
                  • 6. Re: commit on remote database?
                    Solomon Yakobson
                    vis1985 wrote:
                    Can we fire commit on remote database?
                    Commit what? If you want to issue commit/rollback in other session - you can't. If you want to commit changes your session made, including changes made to remote database - simply issue commit. If you want to commit changes made to remote database without committing changes made locally you can use pragma autonomous transaction:
                    SQL> select count(*) from emp1
                      2  /
                    
                      COUNT(*)
                    ----------
                             0
                    
                    SQL> select count(*) from emp1@sol10
                      2  /
                    
                      COUNT(*)
                    ----------
                             0
                    
                    SQL> declare
                      2      procedure p1
                      3        is
                      4            pragma autonomous_transaction;
                      5        begin
                      6            insert into emp1@sol10 select * from emp@sol10;
                      7            commit;
                      8      end;
                      9  begin
                     10      insert into emp1 select * from emp;
                     11      p1;
                     12  end;
                     13  /
                    
                    PL/SQL procedure successfully completed.
                    
                    SQL> rollback
                      2  /
                    
                    Rollback complete.
                    
                    SQL> select count(*) from emp1
                      2  /
                    
                      COUNT(*)
                    ----------
                             0
                    
                    SQL> select count(*) from emp1@sol10
                      2  /
                    
                      COUNT(*)
                    ----------
                            14
                    
                    SQL> 
                    SY.
                    • 7. Re: commit on remote database?
                      743101
                      you are probably mistaking if so please fully make us understand what you mean
                      • 8. Re: commit on remote database?
                        MichaelS
                        Can we fire commit on remote database?
                        If you really try to commit remotely you'll encounter following 2064 error:
                        SQL> DECLARE
                           c   INT := DBMS_SQL.open_cursor@oracle ();
                           r   INT;
                        BEGIN
                           DBMS_SQL.parse@oracle (c, 'begin commit; end;', 1);
                           r := DBMS_SQL.execute@oracle (c);
                           DBMS_SQL.close_cursor@oracle (c);
                        END;
                        /
                        Error at line 1
                        ORA-02064: distributed operation not supported
                        ORA-06512: at line 1
                        ORA-06512: at "SYS.DBMS_SQL", line 1575
                        ORA-06512: at line 6
                        • 9. Re: commit on remote database?
                          MichaelS
                          Ran into an ORA-02064 , but that has nothing to do with OP's question.
                          I think it might even have something to do with OP's question ;)
                          at least when you take his subject literally ...