Oracle session termination
Posted on 2010-11-15
I need your help to fix one issue. I’m using SQL*plus 10.2.0.1.0 as oracle client and Oracle 10.2.0.3.0 as a database. In our organization, we have a separate LINUX box (AEDB01) for batch jobs and which will connect database using TNS name.
I have a one process which invokes one procedure. Basically procedure contains 3 MERGE statements to insert/update data in 3 target tables based on staging table. We have seen a couple of instances of the process running from AEDB01 have run for a long time due to the size of the uploads and, after a while, Oracle would report the session as TERMINATED. There was no indication in the job logs or in the database logs (alert.log) that this is a termination instigated by the database such as a transaction rollback or some error in the code. The database session was eventually cleaned up by Oracle but remained on AEDB01 as a shell script, so I had to clean that up manually.
FYI, I had invoked job using NOHUP command in AEDB01 box even though the oracle session will terminate after a while.
I’m suspecting that this is caused by some network timeout parameter which is killing the session after a period of time. I would like to know what parameter do I need to setup to in order to session alive until job completes.
Any help greatly appreciated.