Tag Archives: sql prompt

How to change SQL  prompt to show connected user and database name

How to change SQL  prompt to show connected user and database name

Ans: Connect as oracle user and add the following line at the end of th glogin.sql script which is located under $ORACLE_HOME/sqlplus/admin

[oracle@rac1 ~]$ cd $ORACLE_HOME/sqlplus/admin
[oracle@rac1 admin]$ pwd
/u01/app/oracle/product/12.2.0.1/sqlplus/admin
[oracle@rac1 admin]$ ls -ltr
total 20
-rw-r--r--. 1 oracle dba  813 Mar  7  2006 plustrce.sql
-rw-r--r--. 1 oracle dba  226 Apr 29  2015 libsqlplus.def
-rw-r--r--. 1 oracle dba 2683 Jan  6  2016 pupbld.sql
drwxr-xr-x. 2 oracle dba 4096 Jan 13  2017 help
-rw-r--r--. 1 oracle dba  422 Aug 29 09:27 glogin.sql
[oracle@rac1 admin]$
[oracle@rac1 admin]$ cat glogin.sql
--
-- Copyright (c) 1988, 2005, Oracle.  All Rights Reserved.
--
-- NAME
--   glogin.sql
--
-- DESCRIPTION
--   SQL*Plus global login "site profile" file
--
--   Add any SQL*Plus commands here that are to be executed when a
--   user starts SQL*Plus, or uses the SQL*Plus CONNECT command.
--
-- USAGE
--   This script is automatically run
--
[oracle@rac1 admin]$ 
-- Connect as oracle user and add the following line at the end of the glogin.sql script.
-- Added by Rajasekhar Amudala
set sqlprompt "_user '@' _connect_identifier > "
[oracle@rac1 admin]$ cat glogin.sql
--
-- Copyright (c) 1988, 2005, Oracle.  All Rights Reserved.
--
-- NAME
--   glogin.sql
--
-- DESCRIPTION
--   SQL*Plus global login "site profile" file
--
--   Add any SQL*Plus commands here that are to be executed when a
--   user starts SQL*Plus, or uses the SQL*Plus CONNECT command.
--
-- USAGE
--   This script is automatically run
--
-- Added by Rajasekhar Amudala
set sqlprompt "_user '@' _connect_identifier > "
[oracle@rac1 admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Aug 29 09:28:14 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SYS @ CDB2 > select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
CDB2      READ WRITE

SYS @ CDB2 > show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           READ WRITE NO
         5 PDB3                           READ WRITE NO
         6 PDB4                           READ WRITE NO
SYS @ CDB2 >
SYS @ CDB2 > connect sys@PDB1 AS SYSDBA
Enter password:
Connected.
SYS @ PDB1 >
SYS @ PDB1 > show con_name

CON_NAME
------------------------------
PDB1
SYS @ PDB1 >

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com