Re: problem: insert into remote table (dblink) hangs after migration 8.0.6 to 9.2.0

On Fri, 17 Nov 2006 05:47:38 -0800, jan.ridder wrote:

> Hi, all.
>
> I have a distributed database system, that, until recently, consisted of
> Oracle 8.0.6. databases.
> It has a main database, where most of the action takes place, and 10-20
> smaller remote databases, connected with dblinks.
>
> Recently we migrated the main database to Oracle 9i. The remote databases
> remained 8.0.6.
>
> After adjusting the tnsnames to the new global naming conventions, all the
> dblinks seem to work correctly. We can do selections and small inserts, so
> the dblinks work fine.
>
> But sometimes, when our application tries to insert data on several remote
> tables (via a synonym) in one PL-SQL block, it hangs on the first insert.
>
> When I use TOAD to look at the sessions on both databases, we see: -
> there are no blocked or blocking locks on either sides - the
> session on the main database waits: SQL*Net break/reset to dblink
> - the session on the remote database waits: SQL*Net more data to
> client
>
> Any suggestions on what to check, where to look, or links to relevant
> documentation are more than welcome.
>
> JanR

Jan,

What a coincidence. We had the same problem yesterday. Our configuration is a 9i (9.2.0.7) inserting in a 8i (8.1.7.4) databae through a db-link. Take a look at Metlink note 276132.1
I did resolve the problem converting the insert ... into ... select to a loop that insert the records.

regards,

Ivan Received on Fri Nov 17 2006 - 11:56:13 CST