Modify sqlplus prompt in Oracle Database

Default sqlplus prompt is very simple once you connect to a database.

sqlplus / as sysdba
SQL>

This article shows how you can enhance it using sqlplus command SET SQLPROMPT and predefined user  variables.

sqlplus / as sysdba
SQL> set sqlprompt "DBAORA.COM> "
DBAORA.COM>

There are many predefined user variables in Oracle Database

sqlplus / as sysdba

SQL> define
DEFINE _DATE           = "07-DEC-14" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "ORA12C" (CHAR)
DEFINE _USER           = "SYS" (CHAR)
DEFINE _PRIVILEGE      = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1201000200" (CHAR)
DEFINE _EDITOR           = "ed" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 12c Enterprise 
Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics 
and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE      = "1201000200" (CHAR)

They can be used to modify default prompt

sqlplus / as sysdba
SQL> set sqlprompt "_user'@'_connect_identifier'> '"
SYS@ORA12C>

You can define your own substitution variable and refer to it when you set sqlplus prompt

sqlplus / as sysdba
SQL> col container_name new_value _container_name noprint
SQL> select sys_context('userenv', 'con_name') as container_name
from dual;

SQL> define _CONTAINER_NAME
DEFINE _CONTAINER_NAME = "CDB$ROOT" (CHAR)
SQL> set sqlprompt "_user'@'_connect_identifier':'_container_name'> '"
SYS@ORA12C:CDB$ROOT>

The problem is sqlprompt settings are gone once you are disconnected. So to make them permanent modify following file $ORACLE_HOME/sqlplus/admin/glogin.sql

vi $ORACLE_HOME/sqlplus/admin/glogin.sql

and put following lines

set verify off
set term off
col con_name new_value _container_name noprint
select sys_context('userenv', 'con_name') as con_name
from dual;
set sqlprompt "_user'@'_connect_identifier':'_container_name'> '"
set verify on 
set term on

so every time you connect to a database you should see more details about your connection

sqlplus / as sysdba

SYS@ORA12C:CDB$ROOT>

It will help identify where commands are executed and by who

!!! WATCHOUT !!! – your prompt is not modified on the fly. It happens only when you connect and disconnect from sqlplus

SYS@ORA12C:CDB$ROOT> select CON_ID, name from v$containers;

    CON_ID NAME
---------- ------------------------------
     1 CDB$ROOT
     2 PDB$SEED
     3 PORA12C1

SYS@ORA12C:CDB$ROOT> alter session set container = PORA12C1;

Session altered.

SYS@ORA12C:CDB$ROOT> show con_id

CON_ID
------------------------------
3
SYS@ORA12C:CDB$ROOT> show con_name

CON_NAME
------------------------------
PORA12C1

Have a fun 🙂

Tomasz

Leave a Reply

Your email address will not be published. Required fields are marked *