[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.
A slight more complete query to check parameters which are not set the same between cdb$root and the PDB is this one:
with pdb_params as (select pdb_name,name,trim(both '''' from value$) value$
from pdb_spfile$ left join dba_pdbs on (CON_UID=pdb_uid)
where con_id>1 and bitand(nvl(spare2,0),1)=0 ),
cdb_params as (select name, value,inst_id,con_id from gv$parameter ),
spfile_params as (select name,value, sid from v$spparameter where isspecified='TRUE')
select pdb_name, pdb_params.name, value$ pdb_value, cdb_params.value root_value, spfile_params.value spfile_value
from pdb_params, cdb_params, spfile_params
where pdb_params.name(+)=cdb_params.name and cdb_params.name(+)=spfile_params.name
and (value$!=cdb_params.value or value$!=spfile_params.value)
union all
select case when sys_context('USERENV','CON_NAME') !='CDB$ROOT' then 'CALL THIS SCRIPT FROM CDB$ROOT' end pdb_name,
null,null,null,null from dual
order by 1,2;
Now, the set or unset parameters do not work the same way, as I expected, it trigger some strange behaviours.
(more…) 
