Twitter Linkedin RSS
Close
Oracle

23ai adds PING to SQL*Plus

Sean D. StuberLeave a comment

For all the amazing and grand new features coming in 23ai (formerly 23c) for the database… sometimes it’s the little things that are really appreciated.

SQL*Plus in 23ai has a new PING command that mostly takes the place of TNSPING. This is fantastic news for all the fans of the Instant client that never included this handy utility. I came across requests for the functionality so often I wrote an article a few years ago with some alternatives for this missing functionality.

Now, with just the standard 23ai client SQL*Plus you can issue PING command and it will give you a sqlnet round trip timing to your currently connected database.

SQL> ping
Ok (1.108 msec)

Alternately, you can give it a connection identifier and it will send a message to the corresponding listener for that identifier, similar to what TNSPING does. In this example I’ll ping one of my older 21c databases. You can use the command with or without the optional LISTENER keyword

SQL> ping pdb21a
Network service name mapping file: /opt/oracle/product/23ai/dbhomeFree/network/admin/tnsnames.ora
Attempting to contact: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=testserver)(PORT=1521))(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=pdb21a)))
Ok (0.477 msec)

SQL> ping listener pdb21a
Network service name mapping file: /opt/oracle/product/23ai/dbhomeFree/network/admin/tnsnames.ora
Attempting to contact: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=testserver)(PORT=1521))(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=pdb21a)))
Ok (0.422 msec)

Note, this is similar output, but not exactly the same as TNSPING.

$ tnsping pdb21a

TNS Ping Utility for Linux: Version 23.0.0.0.0 - Production on 28-SEP-2023 17:54:45

Copyright (c) 1997, 2023, Oracle.  All rights reserved.

Used parameter files:
/opt/oracle/product/23c/dbhomeFree/network/admin/sqlnet.ora

Used LDAP adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=testserver)(PORT=1521))(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=pdb21a)))
OK (0 msec)

SQL*Plus reports the tnsnames.ora file, but on this system I’ve configured LDAP name resolution. So, while the SQL*Plus PING was still able to resolve the name and establish communication with the listener, it incorrectly reported the use of the tnsnames.ora file which does not have the pdb listed in it.

Also of note, the SQL*Plus PING command returns fractional milliseconds in its timings. TNSPING always return an integer value, even if that means rounding down to 0, as in the example above.

Another interesting feature, or rather, lack thereof, is pinging an identifier that can’t be resolved doesn’t produce an error or any kind of message.

SQL> ping does_not_exist
SQL>

In the event the listener is unreachable, neither PING or TNSPING can communicate but they report the error differently. 

SQL> ping pdb21a
Network service name mapping file: /opt/oracle/product/23ai/dbhomeFree/network/admin/tnsnames.ora
Attempting to contact: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=testserver)(PORT=1521))(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=pdb21a)))
SP2-1683: Ping failed with error TNS-12541.
Help: https://docs.oracle.com/error-help/db/sp2-1683/
SQL> exit

$ tnsping pdb21a

TNS Ping Utility for Linux: Version 23.0.0.0.0 - Production on 29-SEP-2023 11:56:24

Copyright (c) 1997, 2023, Oracle.  All rights reserved.

Used parameter files:
/opt/oracle/product/23c/dbhomeFree/network/admin/sqlnet.ora

Used LDAP adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=testserver)(PORT=1521))(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=pdb21a)))
TNS-12541: Cannot connect. No listener at host 192.168.1.19 port 1521.
 TNS-12560: Database communication protocol error.
  TNS-00511: No listener
   Linux Error: 111: Connection refused




Despite these minor differences, this is still a welcome improvement to the classic tool.  I'm looking forward to the full database general release and now the client as well.





If you had an earlier preview-install of the Free 23c release, you might not have this functionality.  I had an older one I downloaded in April that didn't have PING in it.  To do these tests, I installed a fresh copy downloaded after it came out of "preview" status.

Leave a Reply