How to switch to the only PDB in Oracle 12+?

Question

Using Oracle's vagrant boxes, you can easily add scripts that are run post installation by putting them in the userscripts directory. I want to create my standard users, which is easy (CREATE USER etc...). However, those user needs to be created in the PDB and not in CDB$ROOT.

So, how do I switch from sys / as sysdba, which is connected to CDB$ROOT, to the one and only PDB in the database? The name of the PDB should not be hardcoded, as it is controlled by a parameter in the Vagrantfile. The script should run successfully without intervention.

I got so far, this code is working, but butt-ugly:

COLUMN pdb_name NEW_VALUE mypdb
SELECT pdb_name
  FROM (
        SELECT pdb_name,
               RANK() OVER (ORDER BY CREATION_SCN) r
          FROM dba_pdbs p1
         WHERE pdb_name <> 'PDB$SEED'
       )
 WHERE r = 1;

ALTER SESSION SET CONTAINER=&mypdb; 

There must be an easier way...

Answer 1

If it is true that this is the "one and only" pdb, why all the ordering? Don't you just need

COLUMN pdb_name NEW_VALUE mypdb
SELECT pdb_name
FROM dba_pdbs p1
WHERE pdb_name <> 'PDB$SEED'

But since you are using the vagrant file, you could have your scripts do

grep ORACLE_PDB Vagrantfile | awk ...

to get the name of the PDB and then set TWO_TASK or similar to that.