Simon Krenger

When working with the new multitenant architecture of Oracle 12c, one is often confronted with questions like “Ok, is this or that possible in the pluggable database? And where is that value stored?”. In this article, I want to show you where the parameters of the pluggable databases are stored. Hint: there is no SPFILE for a pluggable database!

Consider the following container database (CDB), named “cdb2”, with the following pluggable databases (PDBs). Using SQL*Plus, connected to the CDB, we can issue show pdbs to get a list of the PDBs and their status:

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1_2                         READ WRITE NO
         4 PDB1_1                         READ WRITE NO
         6 PDB1_3                         READ WRITE NO

So let’s check what the parameter open_cursors is set to in the CDB. The value of the parameter is set to 300:

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> show parameter open_cursors

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     300

Now let’s change to the PDB “pdb1_1” and confirm, that the value is the same there:

SQL> alter session set container=pdb1_1;

Session altered.

Elapsed: 00:00:00.04
SQL> show parameter open_cursors;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     300

Ok, looks great. Now, within the PDB, we change the value of open_cursors to 310. We can do this because the parameter is modifiable on PDB level. Note that I want to apply this change to “MEMORY” and “SPFILE”, so I specify “BOTH”:

SQL> alter system set open_cursors=310 scope=both;

System altered.

Elapsed: 00:00:00.01
SQL>  show parameter open_cursors;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     310

Alright, just what we wanted. But now let’s see what the value looks like in the CDB:

SQL> alter session set container=cdb$root;

Session altered.

Elapsed: 00:00:00.01
SQL> show parameter open_cursors;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     300
SQL>

Oh, ok. So when we change the value of a parameter inside a PDB, the value of the parameter in the CDB does not change at all. This means that the SPFILE of the CDB is unchanged. Also, a PDB does not have a SPFILE! So where is the new value of the parameter saved?

Internally, it is saved in the pdb_spfile$ table:

SQL> desc pdb_spfile$
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DB_UNIQ_NAME                              NOT NULL VARCHAR2(30)
 PDB_UID                                   NOT NULL NUMBER
 SID                                       NOT NULL VARCHAR2(80)
 NAME                                      NOT NULL VARCHAR2(80)
 VALUE$                                             VARCHAR2(4000)
 COMMENT$                                           VARCHAR2(255)
 SPARE1                                             NUMBER
 SPARE2                                             NUMBER
 SPARE3                                             VARCHAR2(128)

But since this is an internal table, there must be a view around somewhere. And indeed there is: You should query the v$system_parameter dynamic performance view (documentation link) to get the parameters for your PDBs:

SQL> select name, con_id, value from v$system_parameter where name='open_cursors';

NAME                               CON_ID VALUE
------------------------------ ---------- ------------------------------
open_cursors                            0 300
open_cursors                            4 310

2 rows selected.

Elapsed: 00:00:00.03
SQL>

Note that the parameter is set to 300 for the CDB (con_id 0) and set to 310 for the PDB (con_id 4). This way you can figure out how the parameters are set for the different container and pluggable databases. Go read up on the view documentation (link above) to see which fields you can query using this view.