Oracle SQL*Plus

Start SQL*Plus

SQLPLUS  [ [options]  [logon|/NOLOG]  [@sql-script  [arg-1, [arg-2…]]   ] ]

Options

`-help`
`-version`
`-c` or `-compatibility`
`-f` or `-fast`	Using `-fast` is intended to improve general performance. It changes the default values for `arraysize`, `lobprefetch`, `pagesize`, `rowprefetch` and `statementcache`.
`-l` or `-logon`	Only one attempt to login.
`-m` or `-markup`	to set markup options (csv or html options).
`-nologintime`	Don't display last successful login time
`-r` or `-restrict`	To set the restriction level (1, 2 or 3).
`-silent`

Syntax of logon

  username[/password][@connect_identifier]  | /
[ AS {SYSASM|SYSBACKUP|SYSDBA|SYSDG|SYSOPER|SYSRAC|SYSKM}]
[ edition=value ]

Note that the at symbol (@) is also used for @sql-script.

sqlplus rene/theSecret@serverName:1521/serviceName

@sql-script

If @sql-script is specified, SQL*Plus will execute it. The symbol is probably borrowed from the alias of the start command.

The value of sql-script can either be an URL or a local filename.

Note that the at symbol (@) also has also a meaning in the logon part.

Profiles

When SQL*Plus is started, it executes the site profile (glogin.sql) and the user profile (login.sql), but see also the -restrict option.

Using a net service name

Assuming the net service name is nsn:

sqlplus user/password@nsn

Net service names can be looked up in several places, for example Oracle Names.

With easy connection identifier

Connect to Oracle service named serviceName on machine/server named machineName on port number port:

sqlplus user/password@machineName:port/serviceName

Starting with Oracle 19, there is an improved variant named easy connection plus

«Long» connection string

sqlplus user/password@"(description=(address=(protocol=tcp)(host=192.168.0.227)(port=1521))(connect_data=(sid=ora19)))"

Omitting password

The password (after the first slash (/)) can be omited in which case SQL*Plus asks for the password.

Client side wallet

It's also possible to store credentials in a * client side wallet* in which case the password does not need to be supplied at all. In such a scenario, the user connects to the database using a simple slash:

sqlplus /@DB

Preliminary connections

A preliminary connection does not not create a process state and/or session state object and can therefore be used as a light connection with limited access to the SGA to at least shutdown and restart a database or execute oradebug commands if the instance seems to be hanging or the maximum number of processes has been reached (ORA-00020 maximum number of processes exceeded).

$ sqlplus -prelim / as sysdba
SQL> oradebug …
SQL> shutdown …

$ sqlplus /nolog
SQL> set _prelim on
SQL> connect / as sysdba

cmd.exe / Character set (UTF8)

When working with UTF-8 encoded files and cmd.exe or PowerShell on Windows, the environment variable NLS_LANG should be set to .AL32UTF8 and the code page set to 65001:

C:\> set NLS_LANG=.AL32UTF8
C:\> chcp 65001
C:\> sqlplus rene/theUglySecret@ora19
…

Displaying the value of NLS_LANG in Windows

When SQL*Plus is run in Windows, it takes the value for NLS_LANG from the environment variable with the same name, and if this is not set from the registry (which might also not be set).

In order to display the actual value that SQL*Plus uses for NLS_LANG, the following obscure syntax can be used which responds in an error message but also reveals the value:

SQL> @[%NLS_LANG%]
SP2-0310: unable to open file "[AMERICAN_AMERICA.WE8MSWIN1252]"

Btw, the same works also in Linux with @[$NLS_LANG].

Colors when started in cmd.exe

Colored output (using ANSI Escape Codes) in SQL*Plus, when started from cmd.exe, can be enabled with the following simple invocation of powershell.exe (See also writing colored output to the console with VBScript):

host powershell.exe -noProfile -executionPolicy bypass -c "exit"

After enabling colors, they can be used like so:

begin
   dbms_output.put_line(chr(27) || '[93m' || 'Warning: ...'); -- Print something in yellow
   dbms_output.put_line(chr(27) || '[91m' || 'Error  : ...'); -- Print something in red
   dbms_output.put_line(chr(27) || '[0m'  || 'Normal text '); -- Reset color
end;
/

Clearing the console:

select chr(27) || '[2J' from dual;

True color:

select chr(27) || '[38;2;' || (level-1) || ';0;0mt' || chr(27) || '[0m' from dual connect by level <= 256;
select chr(27) || '[48;2;' || (level-1) || ';0;0mt' || chr(27) || '[0m' from dual connect by level <= 256;

Some commands

`attribute`
`accept`
`copy`	Copies the result of an `select` statement to another database.
`break`
`clear`	`clear bre[aks] \| buff[er] \| col[umns] \| comp[utes] \| scr[een] \| sql \| timi[ng]`
`column`
`connect`
`define`
`describe`	Shows basic(!) characteristics of tables, views, object types or PL/SQL objects.
`edit`
`execute`	Execute a single PL/SQL statement.
`exit`	terminates an SQL*Plus session
`host`	executes a shell (or, on Windows, a cmd.exe) command without leaving or terminating SQL*Plus
`new_value`
`password`
`print`
`recover`
`set`
`shutdown`
`show`	? `show parameters` and `show spparameters`
`spool`	starts writing output to a file.
`start` (or `@`)	runs (executes) SQL statements in an SQL file (script).
`store`	Store attributes of the current SQL*Plus session in a script.
`timing`	measure elapsed time for sections in SQL Scripts
`whenever sqlerror`
`variable`	Define a bind variable

TODO

The standby keyword for Data Guard standby databases.