BULK COLLECT via DB LINK in Oracle8i

[ruse7013]

Hello,

Environment: Oracle8i

Scenario:
A package/procedure is executed under object owner "A" in db_schema "A". A cursor is retrieving records from a table "B1" from db_schema "B" (via a db_link!) using BULK COLLECT. Ultimately, all the records are INSERTed (via a db_link!) with FORALL INTO a table "B2" from db_schema "B".

Error:
The following error was returned by the Oracle8i database: ORA-02046 distributed transaction already begun

Question:
Is there any conflict between using BULK COLLECT and SELECTing/INSERTing records over database links in Oracle8i?[/color red]

Thank you.
<ruse7013>

 

[HimanB]

Hi,
This error comes when you are using Transparent Gateway. Try increasing the parameter distributed_transactions in your init.ora file.

Regards
Himanshu

 

[ruse7013]

Hello Himanshu,

I found file init.ora in $ORACLE_HOME/dbs

However, there is not a parameter called distributed_transactions in file init.ora

What can I advice my DBA as fas as setting that parameter? What's the best (or safe) value?

Thank you.
<ruse7013>

 

[ruse7013]

Hello Himanshu,

Actually, I found the init.ora as initSID.ora. However, I wouldn't be able to open it due to unsufficient access permissions.

Also, I found this:
"Consider increasing the value of the DISTRIBUTED_TRANSACTIONS when an instance regularly participates in numerous distributed transactions and the ORA-2042 is frequently returned. Increasing the limit allows more users to concurrently issue distributed transactions."

In my case, the error that I got is ORA-02046, which is slightly different, e.g.:
"A server session received a BEGIN_TRAN remote procedure call before finishing with a previous distributed transaction."

Any idea about rectifying ORA-02046? Thank you.

Regards,
<ruse7013>

 

[HimanB]

Hi,
Unforetunately I could not find anything related to this.

As per Oracle this is an internal error & you need to contact Cutomer support for the resolution.

Regards
Himanshu

 

[HimanB]

Ruse,
One thing which you may do to Check the root cause of this error is to Trace the events.
First wrte:
Alter session set events '10046 trace name context forever, level 4';

then run the select to generate the error.

Review trace files for any errors that may of ocurred on the remote side.

Regards
Himanshu