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
-- 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 .
Oracle 12c Index