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?

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.

Answer 2

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