For all the amazing and grand new features coming in 23c for the database… sometimes it’s the little things that are really appreciated.
SQL*Plus in 23c 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 23c 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/23c/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/23c/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. Unfortunately, the SQL*Plus PING error message includes a link that, as of this writing, does not have a description for SP2-1683 – but searching for TNS-12541 once you are on the error portal will take you to descriptive content.
SQL> ping pdb21a Network service name mapping file: /opt/oracle/product/23c/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 refusedDespite 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.