Issue with compiling package with dblink

[June]

Hi,

I wonder if anyone can help with this. My collegues and I are having an issue when compiling a package that contains a dblink. This is a fairly common thing to do here and currently we are trying to compile a package on a 9.2.0.7.0 database with a dblink to a 8.1.7.4.1 database. The first time we compile the package it works fine (and if we compile at short intervals), but if we then leave PL/SQL developer for a while (whilst doing some testing, or going for lunch for example) and then come back to the package, make a small amend and compile again PL/SQL developer seems to take an age to do the compilation (my collegue recently waited for about 20 mins before his developer session completed the compilation).

I had the similar problems running anonymous PL/SQL with a dblink, and also when trying to close down PL/SQL developer (or windows within it) when I have been running SQL with the dblink in it.

Can anyone help with this issue (where we can look, what to look for etc).

Thanks
June

 

[Dezsoe Kados]

One possible reason that comes to my mind is that the database link is disconnected after a specific period of time (Oracle profile of the connecting db-link user, network etc.) and that the reconnect fails (or it is just really really slow).

 

[Gustavo]

You can have lost sessions if the remote code includes CHAR parameters. It is even more likely if you have them in record types passed on as parameters. This is true up to 9.2.0.8 and should be fixed in 10.