LLI Wiki — My Digital Knowledge Base

Connecting to Oracle: environment variables, SQL*Plus and SQLcl

Cheat sheet for Oracle environment variables (Linux, Windows, AIX) and SQL*Plus and SQLcl connection syntaxes.

Quick reference for connecting to Oracle databases from the command line: environment variables on Linux, Windows and AIX, SQL*Plus connection syntaxes and SQLcl.

Environment variables

Linux

Find the running instances, then set the environment of the instance you want to work on:

ps -ef | grep pmon
export ORACLE_SID=orcl
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export PATH=$PATH:$ORACLE_HOME/bin
export NLS_LANG='english_united kingdom.we8iso8859p1'
export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"

The ORACLE_HOME of each instance is listed in /etc/oratab (format SID:ORACLE_HOME:Y|N). A one-liner that shows the running instances and the current environment before setting the variables:

ps -ef | grep pmon && env && export ORACLE_SID=orcl && export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1 && export PATH=$PATH:$ORACLE_HOME/bin

Some installations also need ORACLE_BASE:

export ORACLE_BASE=/u01/app/oracle

Windows

Point TNS_ADMIN at the directory that contains tnsnames.ora (system environment variable), then select the instance and connect:

TNS_ADMIN=C:\oracle\product\19.3.0\client_1\network\admin
set ORACLE_SID=ORCL
sqlplus sys/<PASSWORD>@ORCL as sysdba
sqlplus "sys/<PASSWORD>@ORCL as sysdba"

For local operating-system authentication (no password):

sqlplus "/@ORCL as sysdba"

AIX

Useful shell setup and monitoring commands:

set -o vi          # vi command-line editing (Esc k to recall the previous command)
topas              # live system monitor
errpt -a           # detailed error log
vmstat 2 5         # memory/CPU statistics, 5 samples every 2 seconds
svmon -G           # global memory usage

SQL*Plus connection methods

Via a TNS alias (net service name)

The alias must exist in tnsnames.ora.

sqlplus username/password@NET_SERVICE_NAME

Via Easy Connect (host, port, service name)

sqlplus username/password@host:1521/SERVICE_NAME
sqlplus app_user/<PASSWORD>@oradb01:1521/orcl.example.com

Via a full connect descriptor

Useful when there is no tnsnames.ora entry. -L makes SQL*Plus attempt the logon only once, which avoids a re-prompt loop on a wrong password.

sqlplus -L "app_user/<PASSWORD>@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.0.2.11)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl.example.com)))"
sqlplus -L "app_user/<PASSWORD>@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SID=XE)))"

Use SERVICE_NAME or SID in CONNECT_DATA depending on what the listener exposes.

Connecting as SYSDBA

sqlplus / as sysdba
sqlplus "sys/<PASSWORD>@ORCL as sysdba"

Connecting when the instance is hung: -prelim

The -prelim option attaches to the SGA without creating a full SQL*Plus session, which helps when normal connections hang (for example to run oradebug).

sqlplus -prelim "/ as sysdba"

Editing the command buffer on Linux

define _editor=vim
ed
/

SQLcl

SQLcl is Oracle's command-line client (see the SQLcl documentation). It accepts the same Easy Connect strings and can also connect by SID with host:port:SID.

sql /nolog
CONN sys/<PASSWORD>@oradb01:1521:ORCL as sysdba
CONN monitor_user/<PASSWORD>@oradb01:1521:ORCL
-- by service name
sql sys/<PASSWORD>@oradb01:1521/ORCL.example.com as sysdba
-- by TNS alias
sql "monitor_user/<PASSWORD>@ORCL"

Command history inside SQLcl:

history
history 1 run
history 1 edit

Notes

Mastodon LLIMastodon SocialFosstodon