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, first of all, check in the V$SESSION_WAIT and see what is the session waiting for. Second, look in the alert log and check for any errors (unlikely, you've probably done that). Third, look for $ORACLE_HOME/network/log/sqlnet.log and see if there any errors. You should also check the remote database for any errors. Always look at the bright side of life.

-- 
http://www.mladen-gogala.com