Research on sqlprompt display setting after 12c

As you all know, the new CDB,PDB feature of oracle 12c can change the current container through alter session set container=XXX. I wonder if there is any intuitive way to directly show the changes of this container in sqlprompt. For this reason, there are a series of experiments below.

First, we need to know how to judge the current container

The first kind: show con_name

SQL > alter session set container=pdb1

Session altered.

SQL > show con_name

CON_NAME

-

PDB1

The second kind: SELECT SYS_CONTEXT ('USERENV',' CON_NAME') FROM DUAL

SQL > alter session set container=pdb2

Session altered.

SQL > SELECT SYS_CONTEXT ('USERENV',' CON_NAME') FROM DUAL

SYS_CONTEXT ('USERENV','CON_NAME')

PDB2

So I was thinking about how to visually display the changes in this container in sqlprompt.

The first is to modify the glogin.sql file

[oracle@home01 admin] $vi glogin.sql

Define _ editor = 'vi'

Set time on

Set termout off

Column propmt_c new_value propmt_c

SELECT SYS_CONTEXT ('USERENV',' CON_NAME') as propmt_c FROM DUAL

Set sqlprompt "_ user'@'_connect_identifier (& propmt_c) >"

Then log in to sqlplus for testing

[oracle@home01 admin] $sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun Oct 15 15:34:40 2017

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

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0-64bit Production

With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics

And Real Application Testing options

15:34:40 SYS@PRODCDB (CDB$ROOT) > alter session set container=pdb1

Session altered.

15:35:12 SYS@PRODCDB (CDB$ROOT) > show con_name

CON_NAME

-

Pdb1

The result is that although the container has been changed, the sqlprompt still shows the old CDB$ROOT

Use connect to log in to different containers

15:37:16 SYS@PRODCDB (CDB$ROOT) > conn sys/oracle@pdb2 as sysdba

Connected.

15:37:42 SYS@pdb2 (PDB2) > show con_name

CON_NAME

-

PDB2

15:37:49 SYS@pdb2 (PDB2) >

It was successful this time. When logging in through connect, the display of sqlprompt has changed, but connect_identifier has also become a container library.

Summary of the experiment: you can see that this sqlprompt is only checked when you first enter the sqlplus, and then it will not change again. If you use alter session set container to modify the current container, the result is that sqlprompt will not change with the awareness of the container, but will maintain the con_name obtained when you log in. However, if you log in again using conn sys/oracle@pdbprod1 as sysdba, the sqlprompt will change.

Come on, everybody!