When remote database used in DB Link is down, JDBC hangs

Hello!

I have DB Link defined in my Oracle DB I work with from JSP application which uses
Tomcat's connection pool. When remote DB is available, everything is fine. But when
it goes down (I explicitly shut it down with "shutdown immediate"), JDBC call hangs:

Statement Stmt1 = conn.createStatement(); rs = Stmt1.executeQuery("SELECT something FROM mytable_at_mylink");

Second line causes hang forever (or long enough for me to wait, more that 30 minutes).
If I add this:

Stmt1.setQueryTimeout(5);

then it hangs for about 1min45sec, then throws exception "end of line in communication
channel". Unfortunately, 1min45sec is too much to wait for the end user. Also, it resets
my connection to my DB (not remote one), so it is no longer usable, which costs me
something to recover.

It looks like the error occurs only when connection I use had successful "history" of
being able to connect via DB Link. If I get connection not from Tomcat's connection
pool, but force a new connection, everything works fine (something to do with session
using DB Link).

Has anyone had similar problem? Is a workaround different from creating a new
connection available?

Thanks! Received on Fri Feb 10 2006 - 08:00:59 CST