12c: DISPLAY CDB/PDB NAME IN SQL PROMPT

12c: DISPLAY CDB/PDB NAME IN SQL PROMPT

While playing with 12c, I had to repeatedly issue commands to identify the CDB/PDB I was currently in.
So, I decided to modify my glogin.sql to set my SQL prompt to reflect my current CDB/PDB.

- Edit glogin.sql file

$vi $ORACLE_HOME/sqlplus/admin/glogin.sql
-- Add following lines to it

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 that script is working as expected

– Connect to container database CDB1

SQL> conn sys/oracle@cdb1 as sysdba
- Verify that the prompt displays current container (CDB$ROOT) and container database (CDB1)

CDB$ROOT@CDB1> sho con_name
CDB$ROOT

CDB$ROOT@CDB1> sho parameter db_name
db_name                              string      cdb1
- Connect to pluggable database PDB1

CDB$ROOT@CDB1> conn sys/oracle@pdb1 as sysdba
- Verify that the prompt displays current container (PDB1) and container database (CDB1)

PDB1@CDB1> sho con_name
PDB1

PDB1@CDB1> 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 .

***********************************************************************