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
…

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.