0

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...

wolφi
  • 8,091
  • 2
  • 35
  • 64
  • Even if the name is controlled by a parameter in the Vagrantfile, why does that mean "you don't know the name"? Your code shows that you do (or that you know how to find it out). All that the use of NEW_VALUE does is to allow you to avoid typing (and, perhaps, to *automate* some process so it does not require your intervention - is *that* why you are doing it that way?) Other than that you can connect directly to the pdb, *once you know its name*. –  Jun 05 '20 at 14:55
  • @mathguy: yes, this script should run unsupervised without intervention. Besides, I guess this is a fairly common task, I would like to get a streamlined, well working solution for this task. – wolφi Jun 05 '20 at 15:03
  • What you show (the butt-ugly solution) may be your only choice, and it is almost certainly the easiest. –  Jun 05 '20 at 15:06
  • Just curious How are you creating pdb without hard coding?.You export pdb name as part of environment variables right? –  Jun 05 '20 at 15:10
  • @Sam that might be a good lead. Yes, the PDB name is set in the environment. Frankly, I wouldn't know how to use it in SQL*Plus, though. Found [this](https://stackoverflow.com/questions/30107510/can-sqlplus-read-environment-variables-from-the-machine-is-it-running-on), but it doesn't look better than the code above. – wolφi Jun 05 '20 at 15:15
  • set variables for hostname,password,pdbname and you can start new sqlplus session `sqlplus sys/password@host:1521/pdbname as sysdba` –  Jun 05 '20 at 15:19
  • 1
    Try to install demo schemas in pdb that creates new schemas it has everything you looking for [demo schemas](https://github.com/oracle/db-sample-schemas) you get an idea just going through scripts –  Jun 05 '20 at 15:22
  • @Sam, yes, the sample schemas will have exactly the same problem. Thanks for the pointer to Oracle's open source github, didn't know that. My, do I love stackoverflow! – wolφi Jun 05 '20 at 15:34
  • Another option instead of sys as sysdba login as system user `sqlplus system/password@hostname:1521/pdbname` –  Jun 05 '20 at 15:40

1 Answers1

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.

Connor McDonald
  • 10,418
  • 1
  • 11
  • 16
  • 1
    Yes, you're right. If a second database would appear out of thin air, the code will throw an error, which is absolutely acceptable. – wolφi Jun 06 '20 at 08:16