Daily Archives: 19.11.2020

Strange behaviours on setting PDB parameters 3

This entry was posted in Oracle Oracle 19c on by

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