Forum Stats

  • 3,837,196 Users
  • 2,262,234 Discussions
  • 7,900,219 Comments

Discussions

Switching to PDB from CDB without double quotes seems to fail

User_4LM0M
User_4LM0M Member Posts: 2 Blue Ribbon
edited Dec 19, 2019 11:04AM in Multitenant

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

Answers

  • Sdhamoth-Oracle
    Sdhamoth-Oracle Member Posts: 189 Employee
    edited Dec 19, 2019 7:09AM

    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
    User_4LM0M Member Posts: 2 Blue Ribbon
    edited Dec 19, 2019 7:50AM

    Got it!. Many thanks

  • BPeaslandDBA
    BPeaslandDBA Member Posts: 4,615 Blue Diamond
    edited Dec 19, 2019 11:04AM

    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
    User_EG207 Member Posts: 1 Blue Ribbon

    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