7

For some period time of inactivity, connection to Oracle database are dropped and this leads to error - > end-of-file on communication channel.

  1. Is there any Oracle settings on client machine (sqlnet.ora or some kind of environment variables) which could specify connection timeout and differs from client to client? Or it could be client settings of some "heartbeat" feature (client sends packets in some interval), which prevent connection to be dropped by firewall?

  2. Where I can find setting on server machine for connection timeout due inactivity? Is it possible to see this setting from SQL developer without acquiring physical access to oracle host?

  3. Is it normal behavior for Oracle SQL Developer to be disconnected from Oracle server due inactivity?

Giacomo1968
  • 25,759
  • 11
  • 71
  • 103
user12384512
  • 3,362
  • 10
  • 61
  • 97
  • you ca have a look at [this thread](http://stackoverflow.com/questions/1966247/idle-timeout-parameter-in-oracle) - in the meantime ; may be the 2nd client is not on the same subnet which the firewall authorize to use a more long access to the oarcle server ? –  Aug 19 '12 at 19:37

1 Answers1

6

There is no client setting that would cause a connection to be dropped leading to an ORA-03113 error after some time. And there is no setting on the database server that would cause a connection to be timed out leading to an ORA-03113 error.

The server can enable dead connection detection (DCD) by setting the sqlnet.expire_time setting in the server's sqlnet.ora. That will cause the server to periodically send a probe packet to verify that the client is still up.

Oracle will never drop a connection due to inactivity with an ORA-03113 error. It is possible to configure Oracle to drop idle connections but that would generate a different error. If you are getting an ORA-03113 error, either the firewall is causing the connection to be dropped or there is some other hiccup in the network between the client machine and the server.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • Sounds like expire_time setting could solve my problem, if it is firewall issue. Could you please comment third question, if you had ever used oracle sql developer – user12384512 Aug 19 '12 at 20:14
  • @aauser - I thought I already addressed the third question. Oracle will never drop a connection due to inactivity with an ORA-03113 error. That applies regardless of the client. If you're getting that error after sitting idle for a period of time, it is likely the firewall (or some other network hiccup) causing the problem. – Justin Cave Aug 19 '12 at 21:05
  • @JustinCave : is it a bad idea to provide a dedicated connection for one service and let other services use remaining connections from a connection-pool? – Gaurav Nov 29 '18 at 15:17
  • 1
    @gaurav - It's not inherently a bad idea. It depends on the problem you're trying to solve. – Justin Cave Nov 30 '18 at 00:08