Instance Parameters for Pluggable Databases

Post Published 30-October-2022

UPDATE on 13-March-2024 :  For more information see Oracle Support Document "Initialization parameters in a Multitenant database - Facts and additional information (Doc ID 2101596.1)"

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


-- override them at the PDB level for ORCLPDB1
SQL> alter session set container=ORCLPDB1;

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

SQL> -- note that changing S_A_S 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>
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
SQL> 

-- connect to the PDB and verify that they have changed for the user session
SQL> connect hemant/hemant@orclpdb1
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
SQL>


-- 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
  1  select  pdb.dbid, pdb.con_uid, sp.name, sp.value$
  2  from v$pdbs pdb, pdb_spfile$ sp
  3  where pdb.name = 'ORCLPDB1'
  4  and pdb.con_uid=sp.pdb_uid
  5* order by sp.name
SQL> /

      DBID    CON_UID NAME                                     VALUE$
---------- ---------- ---------------------------------------- ----------------------------------------
4057974068 4057974068 deferred_segment_creation                FALSE
4057974068 4057974068 nls_date_format                          ''
4057974068 4057974068 optimizer_features_enable                '12.2.0.1'
4057974068 4057974068 sort_area_size                           10485760
4057974068 4057974068 workarea_size_policy                     'MANUAL'

SQL>

(The two parameters "deferred_segment_creation" and "nls_date_format") are in my YouTube demonstration).

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)

I have presented a short Video Demonstration as well on YouTube.