Testing Oracle Database Connections – SQL*Plus, Net Manager & TNSPING

How to Test Oracle Database Connection from Client

Testing a connection between your Oracle client and the server is an essential step in configuring your Oracle environment. Whether you’re using Net Manager, editing tnsnames.ora directly, or testing via command line, verifying that the client can reach the instance is crucial.

Let’s explore different methods used to test the database connection, common errors like ORA-01017, and how to verify listener and service name configurations.

Method 1: Test Using Net Manager (GUI-Based)

When you create a TNS entry using Oracle Net Manager, you’ll find a “Test” button at the end of the wizard.

Default Credentials

Oracle uses a default login attempt with:

Username: scott
Password: tiger

Important: Most recent Oracle versions no longer ship with the SCOTT schema, so the test may show:

ORA-01017: invalid username/password; logon denied

Why This Is Still a Valid Test

This error still means your network configuration is working, because:

The client reached the server.
The server validated the login and denied it.

So, despite the login failure, the connection to the instance was successful.

Method 2: Test Using SQL*Plus

You can also directly test connection using SQL*Plus, assuming the TNS alias is configured in tnsnames.ora.

Example Command

sqlplus system@orcl

It will prompt:

Enter password:

If successful, you’ll see:

Connected to:
Oracle Database 21c Enterprise Edition ...

Explanation:

system: Oracle user
orcl: Net service name defined in tnsnames.ora
If login fails, you’ll see ORA-01017 (invalid username/password)

Method 3: Test Using TNSPING

The tnsping utility is a fast way to check if your client can reach the Oracle listener.

Example:

tnsping orcl

Output on Success:

Attempting to contact...
OK (40 msec)

This confirms:

The tnsnames.ora entry is correct
The listener is reachable
The host and port settings are correct

Output on Failure:

TNS-03505: Failed to resolve name

This means:

The alias orcl was not found in tnsnames.ora
OR file paths are not correctly set in $TNS_ADMIN or default directories

Comparison Table: Connection Test Methods

Method	Tool	Checks Network?	Checks Login?	GUI Support	Best Use Case
Net Manager	GUI Tool	Yes	Yes (SCOTT)	Yes	Beginners or guided setup
*SQLPlus**	Command-line	Yes	Yes	No	Real login testing and script connections
TNSPING	Command-line	Yes	No	No	Quick ping test of listener