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:
——————————————————–
Thanks for sharing Anju!
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.”
You are right. I have mentioned it in this post at the end as Note.
Regards
Anju
Will you be attending the AIOUG conference, Sangam 13, in Hyderabad 8-9 November?
No, Presently I don’t have any plans to be there.
Regards
Anju
thank u