Shijumic, thanks for explaining your problem further. If I understand correctly, your perl program is periodically posting small bits of information to the Oracle server, but doing a lot of other tasks on the local computer in between posts to Oracle. So, your Oracle session is not performing some long running task, but it instead is sitting idle for long time periods.

from your initial post, I had assumed that your perl script had submitted some large, time-consuming SQL statement to Oracle, and that Oracle failed to complete it and disconnected your session.

Assuming that your Oracle connection/session is sitting idle for long time periods, then yes, you could try to keep the session alive by periodically performing small SQL tasks just to do something.

However, this may not work anyway. Oracle user accounts can have various resource limits set which may have a FIXED time window set...so that even if you perform keep_alive() type ping queries, your session may still be terminated after a fixed connect time.

If you can connect to Oracle (using SQLPLus) you should be able to query your USER_RESOURCE_LIMITS. Here is what I see for my account on Oracle 10.2:

SELECT * FROM USER_RESOURCE_LIMITS;

RESOURCE_NAME			 LIMIT
-------------------------------- ----------------------------------------
COMPOSITE_LIMIT 		 UNLIMITED
SESSIONS_PER_USER		 UNLIMITED
CPU_PER_SESSION 		 UNLIMITED
CPU_PER_CALL			 UNLIMITED
LOGICAL_READS_PER_SESSION	 UNLIMITED
LOGICAL_READS_PER_CALL		 UNLIMITED
IDLE_TIME			 480
CONNECT_TIME			 UNLIMITED
PRIVATE_SGA			 UNLIMITED

Most of my settings are UNLIMITED, but the IDLE_TIME is set to 480 (minutes?). If you have limits on CPU usage, or CONNECT_TIME, you may be kicked out even if you are not idle.

You could perform some keep_alive() queries if your session is being terminated because of your IDLE_TIME setting, but I would recommend something else: voluntarily disconnect your Oracle session, and re-connect when you need to use it again. Here is why:

When you connect to Oracle, the Oracle server creates an Oracle session for you. This Oracle session consumes various server resources like memory and processes. If your session is idle 99% of the time, then you are consuming session resources which cannot be used by other users. If many users create sessions which sit idle, then the Oracle administrator may have to get more resources (hardware) to support the demand.

If you are spending creating Oracle sessions that last more than 2 minutes, and are spending more than 50% of your time idle, then I would recommend disconnecting and reconnecting as needed. These are of course just a randomly guessed rule of thumb...you can decide what guidelines to follow for your own situation.