Daily Archives: 19.11.2020


Strange behaviours on setting PDB parameters 3

[Tests performed on Oracle 19.8]

All started when I wanted to create a query to check which parameters are set on a PDB and the difference from the CDB$ROOT container.

set pages 110
col pdb_name for a10 
col name for a30 
col value for a20
col pdb_value for a20
col root_value for a20
col source for a10
select a.pdb_name, a.name, a.value PDB_VALUE, b.value ROOT_VALUE,source from
  (select pdb_name,name,value,a.con_id, decode(ismodified,'MODIFIED','PDB SPFILE','PDB$SEED') SOURCE 
   from v$system_parameter a left join dba_pdbs b on (a.CON_ID=b.pdb_id)
   where a.con_id>2 and (ismodified='MODIFIED' or isdefault='FALSE')) a,
  (select 'CDB$ROOT' pdb_name,name,value,con_id,null
   from v$system_parameter where con_id=0) b
where a.name=b.name and a.con_id>2
order by 1,2;

But I know there is also one view called pdb_spfile$ that would show the parameters on the PDB pseudo-spfiles:

col pdb_name for a10 
col name for a20
col value$ for a20
select pdb_name,name,value$ 
from pdb_spfile$ left join dba_pdbs on (CON_UID=pdb_uid) 
where con_id>2 
and bitand(nvl(spare2,0),1)=0
order by name;

The V$SYSTEM_PARAMETER is well documented, while the PDB_SPFILE$ is not.

Now, the set or unset parameters do not work the same way, as I expected, it trigger some strange behaviours.

(more…)