!! Setting SQL prompt in Oracle !!
The default prompt in SQL*Plus is SQL>, does not provide any information like who the user is and what the user is connected as. Prior to Oracle9i, we have to do elaborate coding to get the information as the SQL prompt, from Oracle 9.2.0 we can use SET SQLPROMPT along with SQL*Plus predefined variables.
Whenever SQL*PLUS starts up, it looks for a file named glogin.sql under the directory $ORACLE_HOME/sqlplus/admin. If the file is found, it is read and the containing statements executed. This allows to store settings across SQL*PLUS sessions. From Oracle 10g, after reading glogin.sql, SQL*PLUS also looks for a file named login.sql, in the directory from where SQL*PLUS was and in the directory that the environment variable SQLPATH points to, and reads it and executes it. Settings from the login.sql take precedence over settings from glogin.sql.
In Oracle9i, whenever a user connects through SQL*PLUS, Oracle will execute only glogin.sql, from 10g Oracle will execute login.sql as well. From Oracle 10g, the login.sql file is not only executed at SQL*Plus startup time, but also at connect time as well. So SQL prompt will be changed after connect command.
To set SQL prompt permanently, update $ORACLE_HOME/sqlplus/admin/glogin.sql or login.sql
SQLP[ROMPT] {SQL>|text}
TEXT can be predefined substitution variables which are prefixed with an underscore.
_connect_identifier ======> display connection identifier.
_date ======> display date.
_editor ======> display editor name used by the EDIT command.
_o_version ======> display Oracle version.
_o_release ======> display Oracle release.
_privilege ======> display privilege such as SYSDBA, SYSOPER, SYSASM
_sqlplus_release ======> display SQL*PLUS release.
_user ======> display current user name.
The variable _CONNECT_IDENTIFIER was introduced in SQL*Plus 9.2 and _DATE, _PRIVILEGE and _USER were introduced in SQL*Plus 10.1.
_USER
The variable _USER contains the current user name given by SHOW USER. If SQL*Plus is not connected, the variable is defined as an empty string.
SQL> set sqlprompt "_user>"
The SQL*Plus prompt will shows
SYSTEM>
_PRIVILEGE
When SQL*Plus is connected as a privileged user the variable _PRIVILEGE contains the connection privilege "AS SYSBDA" or "AS SYSOPER" or "AS SYSASM". If SQL*Plus is connected as a normal user the variable is defined as an empty string.
SQL> set sqlprompt "_user _privilege>"
The SQL*Plus prompt will shows
SYS AS SYSDBA>
ASMADM AS SYSASM>
_CONNECT_IDENTIFIER
The variable _CONNECT_IDENTIFIER contains the connection identifier used to start SQL*Plus. For example, if the SQL*Plus connection string is "hr/my_password@MYSID" then the variable contains MYSID. If you use a complete Oracle Net connection string like "hr/my_password@(DESCRIPTION=(ADDRESS_LIST=...(SERVICE_NAME=MYSID.MYDOMAIN)))" then _CONNECT_IDENTIFIER will be set to MYSID. If the connect identifier is not explicitly specified then _CONNECT_IDENTIFIER contains the default connect identifier Oracle uses for connection. For example, on UNIX it will contain the value in the environment variable ORACLE_SID or TWO_TASK. If SQL*Plus is not connected then the variable is defined as an empty string.
SQL> set sqlprompt "&_user@&_connect_identifier>"
or
SQL> set sqlprompt "_user'@'_connect_identifier>"
The SQL*Plus prompt will shows
SYS@east>
SYSTEM@east>
_DATE
The variable _DATE can be either dynamic, showing the current date or it can be set to a fixed string. The date is formatted using the value of NLS_DATE_FORMAT and will show time information. By default a DEFINE or dereference using &_DATE will give the date at the time of use. _DATE can be UNDEFINED, or set to a fixed string with an explicit DEFINE command. Dynamic date behavior is re-enabled by defining _DATE to an empty string.If we want to display current date:
SQL> set sqlprompt "_user _privilege 'on' _date>"
SYS AS SYSDBA on 29-AUG-18>
If we want to display the current date & time:
SYS AS SYSDBA on 29-AUG-18>alter session set nls_date_format = 'mm/dd/yyyy hh24:mi:ss';
Session altered.
SYS AS SYSDBA on 08/29/2018 13:13:45>
_EDITOR
The variable _EDITOR contains the external text editor executable name.
set sqlprompt _editor>
The SQL*Plus prompt will shows
SYS AS SYSDBA on 08/29/2018 13:13:45>set sqlprompt _editor>
ed>define_editor=vi
vi>define_editor=notepad
notepad>
_O_VERSION
The variable _O_VERSION contains a text string showing the database version and available options.
set sqlprompt _o_version>
notepad>set sqlprompt _o_version>
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options>
_O_RELEASE
The variable _O_RELEASE contains a string representation of the Oracle database version number. If Oracle database version is 11.1.0.7.0 then the variable contains "1101000700". The Oracle version may be different from the SQL*Plus version if you use Oracle Net to connect to a remote database.
SYS@east> set sqlprompt _o_release>
1102000400>
SQL> set sqlprompt "_user'@'_connect_identifier:SQL> "
SQL> set sqlprompt "_user'@'_connect_identifier:SQL> "
SYS@ptp29:SQL>
SYS@ptp29:SQL>
To reset to the default SQL prompt,
SQL> set sqlprompt 'SQL>'
for 12c sqlprompt :
edit glogin.sql
define gname=idle
column global_name new_value gname
set heading off
set termout off
col global_name noprint
select upper(sys_context ('userenv', 'con_name') || '@' || sys_context('userenv', 'db_name')) global_name from dual;
set sqlprompt '&gname> '
set heading on
set termout on
test it now
connect root container & container database
sqlplus / as sysdba
CDB$ROOT@CDB1> show con_name
CDB$ROOT
CDB$ROOT@CDB1> sho parameter db_name
db_name string cdb1
now connect pluggable database :
conn sys/*****@pdb1 as sysdba
SQL> SHOW CON_NAME
CON_NAME
------------------------------
PDB1
PDB1@CDB1> sho con_name
PDB1
PDB1@CDB1> sho parameter db_name
db_name string cdb1
SQL> ALTER SESSION SET container = pdb1;
Session altered.
SQL> SHOW CON_NAME
CON_NAME
------------------------------
PDB1
SQL> sho con_name
PDB1
SQL> sho parameter db_name
db_name string cdb1
Note: SQL prompt will not change and hence will not reflect current PDB name if Alter Session set container =…. is used to modify current container .
SELECT SYS_CONTEXT('userenv','authenticated_identity') authenticated_identity FROM dual;
SELECT SYS_CONTEXT('userenv','enterprise_identity') enterprise_identity FROM dual;
SELECT SYS_CONTEXT('userenv','authentication_method') authentication_method FROM dual;
SELECT SYS_CONTEXT('userenv','current_schema') CURRENT_SCHEMA FROM dual;
SELECT SYS_CONTEXT('userenv','current_schemaid') current_schema_id FROM dual;
SELECT SYS_CONTEXT('userenv','current_user') CURRENT_USER FROM dual;
SELECT SYS_CONTEXT('userenv','session_user') session_user FROM dual;
-- CURRENT_SQL returns the first 4K bytes of the current SQL that triggered the fine-grained auditing event.
SELECT SYS_CONTEXT('userenv','current_sql') current_sql FROM dual;
-- CURRENT_SQLn attributes return subsequent 4K-byte increments, where n can be an integer from 1 to 7, inclusive
SELECT SYS_CONTEXT('userenv','current_sql1') current_sql1 FROM dual;
-- The length of the current SQL statement that triggers fine-grained audit or row-level security (RLS) policy functions or event handlers
SELECT SYS_CONTEXT('userenv','current_sql_length') current_sql_length FROM dual;
-- role is one of the following: PRIMARY, PHYSICAL STANDBY, LOGICAL STANDBY, SNAPSHOT STANDBY.
SELECT SYS_CONTEXT('userenv','database_role') database_role FROM dual;
SELECT SYS_CONTEXT('userenv','db_name') db_name FROM dual;
SELECT SYS_CONTEXT('userenv','db_unique_name') DB_UNIQUE_NAME FROM dual;
-- Returns the source of a database link session.
SELECT SYS_CONTEXT('userenv','dblink_info') dlink_info FROM dual;
SELECT SYS_CONTEXT('userenv','identification_type') identification_type FROM dual;
-- Domain of the database as specified in the DB_DOMAIN initialization parameter
SELECT SYS_CONTEXT('userenv','db_domain') db_domain FROM dual;
SELECT SYS_CONTEXT('userenv','sid') SID FROM dual;
SELECT SYS_CONTEXT('userenv','terminal') terminal FROM dual;
SELECT SYS_CONTEXT('userenv','instance') INSTANCE FROM dual;
SELECT SYS_CONTEXT('userenv','instance_name') instance_name FROM dual;
SELECT SYS_CONTEXT('userenv','ip_address') ip_address FROM dual;
SELECT SYS_CONTEXT('userenv','isdba') isdba FROM dual;
SELECT SYS_CONTEXT('userenv','language') language FROM dual;
SELECT SYS_CONTEXT('userenv','cdb_name') cdb FROM dual;
SELECT SYS_CONTEXT('userenv','con_id') CON_ID FROM dual;
SELECT SYS_CONTEXT('userenv','con_name') con_name FROM dual;
