Switching to PDB from CDB without double quotes seems to fail

User_4LM0M

I've upgraded one of our EBS instances from 11.2.0.4 to 19.3 - all of this works fine. The instance is called TORONTO and has resulted in a CDB called TORONTOCDB and a PDB called TORONTO.

In SQL*Plus, if I connect to the CDB, I can show the PDBs:

show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 toronto                        READ WRITE NO

If I then do alter session set container=toronto;

ERROR:

ORA-65011: Pluggable database TORONTO does not exist.

However, if I do

alter session set container="toronto";

Session altered.

This works. All of the documentation I have read does not seem to need to the double quotes - the question is should I need to use them or not? If not, can anybody point me to the reason why this is please?

Many thanks

Mark

Sdhamoth-Oracle

Without quotes, toronto will be treated as TORONTO (upper case) by Oracle.

But looks like PDB created with name in lower case explicitly. So we have to enclose the pdb name in quotes in order to refer it in correct case.

User_4LM0M

Got it!. Many thanks

BPeaslandDBA

As stated, whomever/whatever created that PDB must have done something like:

CREATE PLUGGABLE DATABASE "toronto" ...

Because the PDB name was in double quotes, the case sensitivity was carried forward and the PDB was created with a lower case name as your SHOW PDBS command has illustrated. So you'll need to always wrap the PDB name in double quotes when you are working with it. I would get terribly frustrated if this were my situation. I'd look to fix it. But that is totally up to you.

If you desire to fix this, simply unplug the PDB and plug it back in with the proper name. Instructions can be found here: https://oracle-base.com/articles/12c/multitenant-pdb-archive-files-for-unplug-and-plugin-12cr2

You will just be unplugging from the CDB and plugging back into the same CDB. This will obviously require downtime.

Cheers,

Brian

User_EG207

Did you check the value of _pdb_name_case_sensitive.

If _pdb_name_case_sensitive is set to TRUE, you will have to use the same case in which the pdb was created and use double quotes.

Try to set it to false, if possible.

Also, look at using ORACLE_PDB_SID