database link and remote connection issues..
We have a sql script... Large script that we would rather not post here. The script connects through a dblink to a remote site at the beginning and gets thousands of records. We then process these records locally which takes around 40 minutes. The link is unused for that time. Then the same script attempts to connect again through the dblink and set a confiirmation flag. But this final confirmation step routinely fails. The script used to work fine when the confirmation was at the beginning of the script.
My understanding is that when you go out through a dblink it creates a session on the remote computer. The session stays active until the local proc ends or the session times out. Successive calls through the dblink from the same proc will reuse the same remote session? Is this true?
So, Is the inactivity period possibly causing this dblink connectivity issue? We put some test sql select statements in the script here and there selecting from dual at the remote site to test the link inbetween major proc calls. Sometimes even these fail. What do I have to do to keep this link active? Is this even my issue?
Like I said this script worked fine when the confirmation piece was at the beginning right before we start downloading files and basically no inactivity period... Now we download files and confirm at the end with a 40min to 2 hour inactivity between.
The remote site as well as the network claim that they are not dropping us... The only piece of information I can provide is that on our side SQLNET.EXPIRE_TIME = 10 and on there side SQLNET.EXPIRE_TIME = 0. I've read that others solved this issue by manipulating this value.
Here are some of the errror message we get in our script...
ORA-03114: not connected to ORACLE
ERROR at line 1:
ORA-02068: following severe error from DMVAIS
ORA-03135: connection lost contact
Edited by: Mark Reichman on Nov 25, 2008 12:47 PM
Edited by: Mark Reichman on Nov 25, 2008 12:57 PM