How to Connect to Oracle Database Using SQL*Plus

SQL*Plus is a command-line tool used to interact with Oracle databases. This guide covers all possible ways to connect using sqlplus, along with examples.

1. Connect as SYSDBA (Administrator Mode)

If you have administrative privileges, you can connect as SYSDBA. This is useful for managing the database.

Command:

sqlplus / as sysdba

Example Output:

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 5 10:00:00 2025
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

This method works only if SQL*Plus is run on the same machine as the database.

2. Connect Using a Username and Password

If you have a valid Oracle database user account, you can connect using your username and password.

Command:

sqlplus username/password

Example:

sqlplus scott/tiger

• Here, scott is the username, and tiger is the password.
• This works only on the local database unless configured for remote access.

3. Connect to a Remote Database Using TNS (Oracle Net Service Name)

If your Oracle database is running on another server, you can use a TNS alias (defined in tnsnames.ora).

Command:

sqlplus username/password@TNS_ALIAS

Example:

sqlplus scott/tiger@orcl

• Here, orcl is the TNS alias configured in tnsnames.ora (usually found in $ORACLE_HOME/network/admin/).
• Make sure the Oracle client is installed and tnsnames.ora is properly configured.

4. Connect Using a Full Connection String

If you don’t have a tnsnames.ora entry, you can connect using a fully qualified connection string.

Command:

sqlplus username/password@//hostname:port/service_name

Example:

sqlplus scott/tiger@//192.168.1.100:1521/orclpdb

• 192.168.1.100 → Oracle server IP/hostname
• 1521 → Oracle listener port
• orclpdb → Oracle service name (or database name)

This method is useful when connecting over the network without configuring tnsnames.ora.

5. Connect Without Providing the Password (Secure Method)

If you don’t want to type the password in the command (for security reasons), use the nolog option and then manually enter credentials.

Command:

sqlplus /nolog

Then, at the SQL prompt, enter:

SQL> connect username@service_name
Enter password:

Example:

SQL> connect scott@orclpdb
Enter password:

This prevents the password from being exposed in the command history.

6. Connect Using an EZCONNECT (Easy Connect) String

The EZCONNECT method allows you to connect without configuring tnsnames.ora.

Command:

sqlplus username/password@hostname:port/service_name

Example:

sqlplus scott/tiger@192.168.1.100:1521/orclpdb

To use EZCONNECT, ensure the following parameter is set in Oracle:

ALTER SYSTEM SET LOCAL_LISTENER='(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=1521))';

This method simplifies remote connections.

7. Connect Using a Wallet (For Secure Passwordless Login)

Oracle wallets allow users to connect without entering a password.

Steps to Configure:

1. Create a secure wallet using Oracle tools:

mkstore -wrl $ORACLE_HOME/network/admin -create
mkstore -wrl $ORACLE_HOME/network/admin -createCredential orclpdb scott tiger

2. Use the wallet to connect:

sqlplus /@orclpdb

This is useful for automated scripts where you don’t want to store passwords.

8. Connect Using SYSOPER (Alternative to SYSDBA)

SYSOPER provides limited administrative privileges.

Command:

sqlplus / as sysoper

This is similar to SYSDBA but with fewer privileges (e.g., it cannot perform database recovery).

Conclusion

SQL*Plus offers multiple ways to connect to an Oracle database, whether you’re working locally or remotely.

Summary of Connection Methods:

Each method is useful in different scenarios. If you’re working in a secure environment, consider using passwordless login (wallets) or secure login (/nolog).