We are replication two equal tables on different database servers with a PL/SQL-package. The according package procedure is periodically activated via a database job. Because different reasons we can't use Oracle Streams or Materialized Views for the replication.
The statement is like "insert into tab (select .... from tab@remote_db".
This works fine if the network connection is ok. But when the network cable will be disconnect at the remote site, the session of the job hangs. When will remove the job, the sessions still hangs.
When we implement a simple procedure with a statement "select 1 from dual@remote_db", we'll get after 70 seconds an ora-12170 error. That's what we expect. But the problem is that the statement above does not generate any error and the session remains and block other sessions after reconnecting the network.
If we disconnect the network cable on the local site, we'll get on error (that's ok).
Would it be helpfully for this problem to change the parameter EXPIRE_TIME in the sqlnet.ora file? What are other solutions. We should solve the problem automatically, there is no administrator who can kills the sessions for example.