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
- Passwords were replaced by
<PASSWORD>; avoid typing them on the command line on shared hosts, as they are visible in the process list and shell history. Prefersqlplus /nologfollowed byCONNECT, or OS authentication. - Host names, instance names, service names, domain and IP addresses are generic examples.
- Oracle client installation and
tnsnames.orasyntax are covered in Oracle's own documentation.