How can I connect to Oracle Database with SQLcl and SQLPlus, but without a TNSNames.ORA file?

Question

You have:

1. an Oracle database
2. an Oracle Client installation, including SQL*Plus
3. the TNS information for #1
4. BUT NO TNSNames.ORA file or the desire to create and maintain one

How can you get your SQL*Plus connection going?

Noyes

Answer 1

sqlplus user/password@(description=(address_list=(address=.......ODS))) 

The text in the () is the information you would see for your service in a TNSNames file. So you can simply use the TNS entry explicitly

Note, use quotes if in Unix else the () are interpreted by the shell.

Or you can use the EZconnect syntax (my preferred method)

sqlplus user/password@//hostname/service_name 

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

Note that for Oracle Database 12/18c multitenant architecture databases, you MUST use the /service_name and not the /SID if you want to connect to a pluggable database.

Note also that we have 2 command-line interfaces now.

SQL*Plus and SQLcl.

SQLcl is java based, and a stripped down version of Oracle SQL Developer. It supports TNS based connections, and also supports the EZConnect syntax. One significant advantage it has over SQL*Plus is that it does not require an Oracle Client installation.

This question was originally answered by Tom on AskTom.

I've updated his answer here to address Oracle 12c Multitenant and SQLcl.

Noyes

thanks @KaushikNayak - we have a added a TON of modern features to compliment the existing SQL*Plus features. You might also like the tab completion, command history, DDL, and ALIAS (build your own commands) features. – Noyes

Answer 2

Assuming, sqlcl executable is present in the current directory, you can use

./sql user/password@host:port/service_name

Parasol

Shouldn't it be user/password@host:port instead of //? – Assurance

Current SQLcl version: it is indeed without // after the @! Many THX @thermz. – Leverhulme

Answer 3

If you don't want to leave password in bash history, use this:

# connect
sql user@host:port/database
# will ask password later
# then connect to your database
sql > conn database

Eleanor

Answer 4

Using a tnsnames.ora file is just one out of four different naming methods, see Parameters for the sqlnet.ora File

Usually ldap and nis are suitable only when you have many Oracle databases running in your premises. Other methods are already provided by @thatjeffsmith

Cavesson