In a multi-tenant container database, since there are many PDB’s per CDB, it is possible for set some parameters for each individual PDB. The SPFILE for CDB stores parameter values associated with the root which apply to the root, and serve as default values for all other containers. Different values can be set in PDBs for those parameters where the column ISPDB_MODIFIABLE in V$PARAMETER is TRUE. The parameters are set for a PDB and are stored in table PDB_SPFILE$ remembered across PDB close/open and across restart of the CDB.
– Currently I have a CDB called CDB1 having one PDB – PDB1.
SQL> sho con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> sho pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
– There is a table pdb_spfile for each of the containers (cdb$root and PDB1)
SQL> select con_id, table_name from cdb_tables where table_name = 'PDB_SPFILE$'; CON_ID TABLE_NAME ---------- -------------------- 3 PDB_SPFILE$ 1 PDB_SPFILE$
– pdb_spfile contains only those parameters which have been specifically set for a container hence currently there are no records
SQL> col container_name for a10
col parameter for a20
col value$ for a30
select container.name container_name, par.name PARAMETER,
par.value$
from pdb_spfile$ par, v$containers container
where par.pdb_uid = container.con_uid
and par.name = 'cursor_sharing';
no rows selected
– Let’s explicitly set cursor_sharing = ‘similar’ in root and check if it is reflected in pdb_spfile$
SQL> alter system set cursor_sharing='similar';
col container_name for a10
col parameter for a20
col value$ for a30
select container.name container_name, par.name PARAMETER,
par.value$
from pdb_spfile$ par, v$containers container
where par.pdb_uid = container.con_uid
and par.name = 'cursor_sharing';
no rows selected
– It does not show any results but v$spparameter has been updated probably implying that spfile for the root is maintained in the operating system only and pdb_spfile does not contain info about parameters in cdb$root.
SQL> select name, value from v$spparameter where name='cursor_sharing';
NAME VALUE
------------------------------ -------
cursor_sharing similar
-- v$parameter shows the value of parameter for root
SQL> col name for a30
col value for a30
select con_id, name, value from v$parameter
where name = 'cursor_sharing';
CON_ID NAME VALUE
---------- ------------------------------ --------
1 cursor_sharing similar
– To see parameters for the CDB and all the PDB’s (except PDB$SEED), v$system_parameter can be accessed. It can be seen that currently it shows only the value for the CDB which will be inherited by all the PDB’s.
SQL>select con_id, name, value from v$system_parameter
where name = 'cursor_sharing';
CON_ID NAME VALUE
---------- ------------------------------ ---------
0 cursor_sharing similar
– Change container to PDB1 and verify that PDB has inherited the value from CDB
SQL> alter session set container=pdb1;
sho parameter cursor_sharing
NAME TYPE VALUE
------------------------------------ ------- ---------------
cursor_sharing string similar
– Since parameter has not been explicitly specified in PDB , v$spparameter shows record for con_id = 0 and null in value column
SQL> select con_id, name, value from v$spparameter where name = 'cursor_sharing'; CON_ID NAME VALUE ---------- ------------------------------ ---------- 0 cursor_sharing
– Let’s check if the parameter can be modified for the PDB
SQL> col ispdb_modifiable for a17
select con_id, name, value, ispdb_modifiable
from v$parameter
where name = 'cursor_sharing';
CON_ID NAME VALUE ISPDB_MODIFIABLE
---------- ------------------------------ ---------- -----------------
3 cursor_sharing similar TRUE
– Since the parameter can be modified in PDB, let us modify its value in PDB to ‘FORCE’
SQL> alter system set cursor_sharing = 'FORCE'; sho parameter cursor_sharing NAME TYPE VALUE ------------------------------------ ----------- ----- cursor_sharing string FORCE SQL> select con_id, name, value from v$parameter where name = 'cursor_sharing'; CON_ID NAME VALUE ---------- ------------------------------ ---------- 3 cursor_sharing FORCE
– v$spparameter shows updated value but con_id is still 0 (bug??)
SQL> select con_id, name, value from v$spparameter where name = 'cursor_sharing'; CON_ID NAME VALUE ---------- ------------------------------ ---------- 0 cursor_sharing FORCE
– Current value of the parameter for PDB can be viewed from root using v$system_parameter
SQL> alter session set container=cdb$root;
select con_id, name, value from v$system_parameter
where name = 'cursor_sharing';
CON_ID NAME VALUE
---------- ------------------------------ -------
0 cursor_sharing similar
3 cursor_sharing FORCE
– Current value in spfile of PDB can be seen from pdb_spfile$
SQL> col value$ for a30
select pdb.name PDB_NAME, par.name PARAMETER, par.value$
from pdb_spfile$ par, v$pdbs pdb
where par.pdb_uid = pdb.con_uid
and par.name = 'cursor_sharing';
PDB_NAME PARAMETER VALUE$
---------- -------------------- ------------------------------
PDB1 cursor_sharing 'FORCE'
– The parameter still has earlier value of similar for cdb$root
SQL> sho parameter cursor_sharing NAME TYPE VALUE ------------------------------------ ----------- ------ cursor_sharing string similar SQL> col name for a30 col value for a30 select con_id, name, value from v$parameter where name = 'cursor_sharing'; CON_ID NAME VALUE ---------- ------------------------------ -------- 1 cursor_sharing similar
– Let’s check if modified value persists across closing / opening of the PDB
SQL> alter pluggable database pdb1 close;
– After PDB is closed, entry in its spfile is still visible but current value cannot be seen as PDB is closed
SQL> col value$ for a30 select pdb.name PDB_NAME, par.name PARAMETER, par.value$ from pdb_spfile$ par, v$pdbs pdb where par.pdb_uid = pdb.con_uid and par.name = 'cursor_sharing'; PDB_NAME PARAMETER VALUE$ ---------- -------------------- ------------- PDB1 cursor_sharing 'FORCE' SQL> select con_id, name, value from v$system_parameter where name = 'cursor_sharing'; CON_ID NAME VALUE ---------- ------------------------------ ------------ 0 cursor_sharing similar
– It can be seen that after PDB is re-opened, the updated
value still persists
SQL> alter pluggable database pdb1 open;
select con_id, name, value from v$system_parameter
where name = 'cursor_sharing';
CON_ID NAME VALUE
---------- ------------------------------ -------------
0 cursor_sharing similar
3 cursor_sharing FORCE
– Let’s verify that parameter change persists across CDB shutdown
SQL> shu immediate;
startup
alter pluggable Database pdb1 open;
select con_id, name, value
from v$system_parameter
where name = 'cursor_sharing';
CON_ID NAME VALUE
---------- ------------------------------ -----------
0 cursor_sharing similar
3 cursor_sharing FORCE
SQL> col value$ for a30
select pdb.name PDB_NAME, par.name
PARAMETER, par.value$
from pdb_spfile$ par, v$pdbs pdb
where par.pdb_uid = pdb.con_uid
and par.name = 'cursor_sharing';
PDB_NAME PARAMETER VALUE$
---------- -------------------- ------------------------------
PDB1 cursor_sharing 'FORCE'
– Now we will change the parameter in PDB spfile only
SQL> alter session set container=pdb1;
alter system set cursor_sharing = 'EXACT' scope=spfile;
– Current value still remains FORCE
sho parameter cursor_sharing
NAME TYPE VALUE
------------------------------------ ----------- ---------
cursor_sharing string FORCE
–Value has been changed to EXACT in SPfile only
SQL> select con_id, name, value
from v$spparameter
where name = 'cursor_sharing';
CON_ID NAME VALUE
---------- ------------------------------ -----------
0 cursor_sharing EXACT
– The above changes can be seen from root as well
SQL> alter session set container=cdb$root; -- The current value is shown as FORCE SQL> select con_id, name, value from v$system_parameter where name = 'cursor_sharing'; CON_ID NAME VALUE ---------- ------------------------------ ---------- 0 cursor_sharing similar 3 cursor_sharing FORCE
– The value in SPFILE is ‘EXACT’ as set
SQL> col value$ for a30
select pdb.name PDB_NAME, par.name
PARAMETER, par.value$
from pdb_spfile$ par, v$pdbs pdb
where par.pdb_uid = pdb.con_uid
and par.name = 'cursor_sharing';
PDB_NAME PARAMETER VALUE$
---------- -------------------- ------------------------------
PDB1 cursor_sharing 'EXACT'
– Let’s close and re-open PDB to vefify that value in spfile is
applied
SQL> alter pluggable database pdb1 close;
alter pluggable database pdb1 open;
select con_id, name, value
from v$system_parameter
where name = 'cursor_sharing'; 2
CON_ID NAME VALUE
---------- ------------------------------ ---------------
0 cursor_sharing similar
3 cursor_sharing EXACT
– Since the value in spfile is same as default, we can remove this
entry by resetting the value of the parameter.
SQL> alter session set container=pdb1;
alter system reset cursor_sharing;
sho parameter cursor_sharing
NAME TYPE VALUE
------------------------------------ ----------- ---------
cursor_sharing string EXACT
– The entry has been deleted from spfile
SQL> select con_id, name, value
from v$spparameter
where name = 'cursor_sharing';
CON_ID NAME VALUE
---------- ------------------------------ -------------
0 cursor_sharing
SQL> alter session set container=cdb$root;
col value$ for a30
select pdb.name PDB_NAME, par.name
PARAMETER, par.value$
from pdb_spfile$ par, v$pdbs pdb
where par.pdb_uid = pdb.con_uid
and par.name = 'cursor_sharing';
no rows selected
I hope this post was useful. Your comments and suggestions are always welcome!!
References:
Oracle documentation
——————————————————————————————-
Related Links:
small correction you said that cursor_sharing to exact but you have set it to similar
– Let’s explicitly set cursor_sharing = ‘exact’ in root and check if it is reflected in pdb_spfile$
SQL> alter system set cursor_sharing=’similar';
Thanks Amit!
Corrected.
Regards
Anju Garg
Excellent Explanation