[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…)