Troubles with using minus over a database link ??

This is one of those questions about procedures where sometimes something goes wrong
for apperently no reason and everybody starts guessing what the problem might be eventually ending in me rewriting all code to bypass some obscure bug......

This is the problem :
We have two different databases (two instances) connected via a database link. Every night
a procedure is started on db2 (local) to synchronise some data with db1 (remote,db1 is leading). For db2 only new or changed rows are required so we use the 'minus' option thus : select x,y,z from db1.table1 minus select x,y,z from db2.table. The minus option is used in several queries; some demanding some easy.

When the procedure is started during the nightbatch it sometimes happens (1 out of 5 times) that we get a 'end of communication channel' - error. The procedure is aborted.

When the trace file is checked the procedure is always aborted at a query using a minus. But since 75% of the procedure consists of cursors based (all neatly openend and closed) on queries with a minus, this can be accidental. Also it doesn't seem to matter if the query is demanding or easy.

So far we haven't come up with a proper explanation, but there are some theories :

• Oracle's helpdesk suggests it is a sqlnet bug. When a remote query is to long gone sqlnet can abort because it thinks the process is killed. There is no proper solution except upgrading. At this moment we do not want to upgrade because of one misbehaving procedure.
  • Some of our DBA's like the blame the use of minus, stating that a minus is slow on remote databases. I think slow performance is very different from complete abortion.
  • It is also suggested that it is a memory conflict between oracle and unix processes. Oracle claims memory already allocated by unix. This is suggested by someone who had the same abortion problems (but it had nothing to do with minus) and the Oracle helpdesk told him it was a sqlnet bug, only to find out it was memory conflict. I doen't have a opinion on this one because this beyond my knowledge about Oracle.

Now i want to rewrite the code without minus to make shure that isn't the problem. But i don't know if it will help.

Is there anybody who can tell me what the problem is ???????  We use Oracle 7.3 and SQL*net 2.3

Rgds,
Michel Received on Wed Jan 27 1999 - 15:17:31 CST