Instance Parameters for Pluggable Databases

Since 12c Oracle has permitted Pluggable Databases with the MultiTenant Architecture. Normally, instance parameters (those in the init<SID>.ora or spfile<SID>.ora) apply to the whole Instance -- thus to all the Pluggable Databases.

However, certain parameters can be changed at the PDB (Pluggable Database) level.

Oracle stores the values for these changed parameters in the PDB_SPFILE$ table in the Root Container.

Here is a simple demonstration (in 19c) :

-- connect to the root and check certain parameter
SQL> connect / as sysdba
Connected.
SQL> show parameter optimizer_features_enable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_features_enable            string      19.1.0
SQL> show parameter sort_area_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sort_area_size                       integer     65536
SQL> show parameter workarea_size_policy

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy                 string      AUTO

-- override them at the PDB level for MYPDB1
SQL> alter session set container=MYPDB1;
Session altered.

SQL> alter system set optimizer_features_enable='12.2.0.1';
System altered.

SQL> alter system set sort_area_size=10485760;
alter system set sort_area_size=10485760
                                       *
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this option

-- note that changing sort_area_size has to be done as "DEFERRED" to apply to subsequent sessions
SQL> alter system set sort_area_size=10485760 deferred;
System altered.

SQL> alter system set workarea_size_policy='MANUAL';
System altered.

SQL> show parameter optimizer_features_enable;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_features_enable            string      12.2.0.1

SQL> show parameter workarea_size_policy;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy                 string      MANUAL

-- connect to the PDB and verify that they have changed for the user session
SQL> connect system/manager@mypdb1
ERROR:
ORA-28002: the password will expire within 7 days
Connected.

SQL> show parameter optimizer_features_enable
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_features_enable            string      12.2.0.1

SQL> show parameter sort_area_size
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sort_area_size                       integer     10485760

SQL> show parameter workarea_size_policy
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy                 string      MANUAL

-- return to the root CDB and check that it retains the old (default) values
SQL> connect / as sysdba
Connected.

SQL> show parameter optimizer_features_enable
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_features_enable            string      19.1.0

SQL> show parameter sort_area_size
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sort_area_size                       integer     65536

SQL> show parameter workarea_size_policy

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy                 string      AUTO
SQL>

-- identify from the CDB where the overriding values are
SQL> connect / as sysdba
Connected.

SQL> get show_orclpdb1_params.sql
    select  pdb.dbid, pdb.con_uid, sp.name, sp.value$
    from v$pdbs pdb, pdb_spfile$ sp
    where pdb.name = 'ORCLPDB1'
    and pdb.con_uid=sp.pdb_uid
    order by sp.name;

      DBID    CON_UID NAME                                     VALUE$
---------- ---------- ---------------------------------------- ----------------------------------------
0278080088 0278080088 deferred_segment_creation                FALSE
0278080088 0278080088 nls_date_format                          ''

0278080088 0278080088 optimizer_features_enable                '12.2.0.1'

0278080088 0278080088 sort_area_size                           10485760

0278080088 0278080088 workarea_size_policy                     'MANUAL'

Thus, we can see that Oracle stores PDB parameters in a table called PDB_SPFILE$ in the Root Container.

These will apply whenever the Pluggable Database is re-opened -- and thus override the parameters for the Root that are from the Server Parameter File (spfile<SID>.ora)