How to check for connection persistence using sqlplus
Last Updated December 25, 2010
Database connectivity issues can be a sign of poor network connectivity. This article presents one way of testing database connectivity across the network over time.
The attached cluster of scripts are meant to be run from a client server to a database server and assume the presence of SQLPlus or SQLPlus client.
They are started from the commandline and by default will establish six individual sessions that after execution, immediately "go to sleep", which is to say that they go into an idle mode without surrendering their connection with the database. After two days, they wake up, complete and exit. A log of the session is created and should show no errors. If during the two day period one or more of the sessions close out prematurely, check the log file for errors. A network issue may have occurred.
There are two versions of these scripts, windows and linux.
You can obtain either of these from the attachments area of the column to the right of this writeup. Look for:
There are three requirements for executing these scripts:
1) they should be executed by the schema owner (typically protect).
2) the schema owner needs to have been granted "execute" privileges on dbms_lock.
3) Once the sessions are established, the client does not "sign off" until the script completes normally. The client side server needs to remain running for two days.
Connectivity issues may (MAY) result in the following error appearing in the log files:
* ERROR at line 1: ORA-03135: connection lost contact