Connecting to Oracle Database Instance Using SQL*Plus – A Beginner’s Guide

What is SQL*Plus?

SQL*Plus is a command-line tool provided by Oracle for interacting with the database. It allows you to execute SQL and PL/SQL commands, run scripts, and manage database connections.

Why You Need to Configure a Connection?

To connect your Oracle client to a remote or local Oracle instance, you need a valid configuration that maps your connection to the database. This involves setting up the tnsnames.ora file and having the correct service name, host, and port details.

Step 1: Locate or Create the tnsnames.ora File

The tnsnames.ora file is typically found at:

$ORACLE_HOME/network/admin

On Windows:

C:\oracle\product\version\network\admin\

Step 2: Sample tnsnames.ora Entry

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rh-server)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

Explanation:

Entry	Description
`ORCL`	Net service name (you can call it anything like `ron`)
`HOST`	IP or hostname of the server
`PORT`	Listener port (default: 1521)
`SERVICE_NAME`	Database instance name (e.g., `orcl`, `xe`, `prod`)

Pro Tip: Custom Net Service Names

You can name the net service entry anything (e.g., ron instead of orcl). Just make sure to refer to that alias during login:

sqlplus username/password@ron

Step 3: Connect Using SQL*Plus

Open a terminal or command prompt and run:

sqlplus plsql_user/password@orcl

If successful, you'll see:

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

Step 4: Test Your Connection Using TNSPING

Before attempting to log in, you can verify the connection using:

tnsping orcl

If successful, output will look like:

Used TNSNAMES adapter to resolve the alias
Attempting to contact...
OK (40 msec)

If failed:

TNS-03505: Failed to resolve name

Step 5: Test via Net Manager

Oracle provides tools like Net Manager or Net Configuration Assistant to visually configure your connection. These allow you to test the connection, although the default test credentials (SCOTT/TIGER) may not exist in newer databases. Even if the login fails, a connection attempt means the network setup works.

Hosts File (Optional for IP Mapping)

If you don’t have a DNS system, add this to your local hosts file:

Windows:

C:\Windows\System32\drivers\etc\hosts

Example entry:

127.0.0.1     rh-server

Now, you can refer to rh-server instead of 127.0.0.1.

When Should You Use This Method?

Setting up client-to-server connections in Oracle environments
Working in enterprise networks without graphical tools
Automating connections for batch or cron jobs
Testing new instance setups and listener configurations

AmantPoint Exclusive Learning Series
© 2025 AmantPoint. All rights reserved. Educational content for developers and learners.