Using sqlplus as tnsping alternative

Question

Is it possible to use sqlplus as an alternative for tnsping since tnsping wasn't installed with the oracle setup. I simply want to check via batch script, whether the definitions from the tnsnames.ora are reachable.

If not, is it possible to subsequently install tnsping?

Answer 1

From the SQL*Plus help doc (sqlplus --help):

Usage 2: sqlplus [ [<option>] [{logon | /nolog}] [<start>] ]
...
<start> is: @<URL>|<filename>[.<ext>] [<parameter> ...]

This means you can supply a path to a script which will run as soon as you connect/login.

The absolute basic example of this is to echo 'exit' to a file, then run that file, e.g.

C:\>echo exit > exit.tmp
C:\>sqlplus /nolog @exit.tmp

SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 14 15:34:35 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


C:\>del exit.tmp

Of course, above I have not logged into a database, so I have not really checked a database. If you want to check a specific tnsnames.ora entry, you will probably need to supply full credentials for that service on the command line (there are possible security ramifications of that), otherwise the 'exit' issued by your script will probably get absorbed by the Username prompt normally issued by sqlplus (either that, or your script will hang at the Username prompt).

J.

Answer 2

There is an "ugly" hack somewhere on the internet showing how to add tnsping into InstantClient installation. For some unknown reason Oracle insist on non-embedding it into InstantClient, although there were asked many times by various customers.

You can use it but:

• Pros: tnsping contacts only the listener, it will not check whether the database is really running. While sqlplus will also check "presence" of SID on the database server side.
• Cons: sqlplus will not tell you how the database name was translated. While tnsping can point you on sqlnet.ora file used and whether TNSNAMES, ONAMES or LDAPs was used.
• Cons: tnsping will also tell you hostname and port for the database

Answer 3

It depends what you actually wanted to check with TNSPING ; the connection itself, or the connection speed, or both. But SQLPLUS will use the same TNS setup, so that way you are testing the same TNS setup.

And if you write a script (even Windows or Linux), you CAN record the time it takes to create such a connection, which would be a similar objective as TNSPING.