Distributed transaction in Oracle ( Over Oracle DBLink)

To fetch the data from one server to and other Oracle server over DBLink, I experienced the following facts of Oracle Distributed transactions:

Security issue:

-          We cannot create Public synonym for the remote object accessed over Private DBLink of other Database.  

-          It’s allowed to create private synonym for remote object, but you cannot grant the access over this synonym to any other schema.

If you try to provide the grants to other schema Oracle raises an error:

             [ORA-02021: DDL operations are not allowed on a remote database]

“In an all you can access remote objects over private DBLink in the same schema where DBLink is created”.

Fetching the Ref Cursor at Remote site:  

                Let’s say we have two sites involved in Distributed transaction, Server1 and Server2. The Ref Cursor opened on Server1 procedure, cannot be fetched at Server2 site. If we try to fetch this cursor oracle raises an exception:

    

[ORA-02055: distributed update operation failed; rollback required

 ORA-24338: statement handle not executed]

                                “We cannot use the Ref Cursor over DBLink” 

Solutions:

1.       Use PL-SQL Data table. OR

2.       Provide select grant and use select command over DBLink from initiator site instead of opening the Cursor.    

Transaction issue:

                If remotely called procedure/Function has Out or In-Out argument, we cannot use commit in remote procedure.

Oracle raises an exception:

[ORA-02064: distributed operation not supported

 ORA-06512: at "DBA.PR_DATATRANFER", line 332

 ORA-06512: at "LIVE.PR_DOWNLOADEDDATA", line 74

 ORA-06512: at line 8]

Solutions:

1.       Use PRAGMA AUTONOMOUS_TRANSACTION (if possible).

2.       Simplify your transaction and check if the transaction initiator site can take care of commit\rollback of transaction.

http://ora-02064.ora-code.com/

     

Use of Global temporary table in Distributed transaction :

                In your distributed transaction, if remote server procedure/Function is using the GTT (on Commit Delete\Preserve rows). Even after using commit or rollback Oracle does not release the locks on temporary table (Ref: http://www.dbasupport.com/forums/showthread.php?t=58263). And when you run the same procedure again oracle raises an exception:

[ORA-14450: attempt to access a transactional temp table already in use

 ORA-06512: at "DBA.PR_DATATRANFER", line 319

 ORA-06512: at "LIVE.PR_DOWNLOADEDDATA", line 74

 ORA-06512: at line 8]

 

Only alternative to release the existing locks is Disconnect and re-connect the session.

Oracle Documentation says that you cannot use GTT in Distributed transactions: http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_73a.htm