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 .

Happy Prompting!

——————————————————–

Related Links:

Home

Database 12c Index

——————————————————–

 

6 thoughts on “12c: DISPLAY CDB/PDB NAME IN SQL PROMPT

    1. Works well … but does not reflect an ALTER SESSION SET CONTAINER as glogin is only run once at login so it is possible for this to create the impression one is in the “test” PDB when, in fact, they are in “prod.”

Leave a Reply to prashanth Cancel reply