[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.
Strange behaviours
ALTER SYSTEM … default SCOPE on PDB
Oracle Documentation 19c only mentions the default SCOPE on CDB root:
"If a server parameter file was used to start up the database, thenBOTHis the default. If a parameter file was used to start up the database, thenMEMORYis the default, as well as the only scope you can specify."
On a PDB, the default SCOPE depends(!) on the action:
Setting a parameter, then default is SCOPE=BOTH :
TVDCDB1.PDB01 SQL> show parameter cursor_sharing
NAME TYPE VALUE
---------------- ----------- ----------------
cursor_sharing string EXACT
TVDCDB1.PDB01 SQL> show spparameter cursor_sharing
SID NAME TYPE VALUE
-------- ---------------- ----------- ----------------
* cursor_sharing string
TVDCDB1.PDB01 SQL> alter system set cursor_sharing=FORCE;
System altered.
TVDCDB1.PDB01 SQL> show parameter cursor_sharing
NAME TYPE VALUE
---------------- ----------- ----------------
cursor_sharing string FORCE
TVDCDB1.PDB01 SQL> show spparameter cursor_sharing
SID NAME TYPE VALUE
-------- ---------------- ----------- ----------------
* cursor_sharing string FORCE
Resetting a parameter, then default is SCOPE=SPFILE :
TVDCDB1.PDB01 SQL> alter system reset cursor_sharing;
System altered.
TVDCDB1.PDB01 SQL> show parameter cursor_sharing
NAME TYPE VALUE
---------------- ----------- ----------------
cursor_sharing string FORCE
TVDCDB1.PDB01 SQL> show spparameter cursor_sharing
SID NAME TYPE VALUE
-------- ---------------- ----------- ----------------
* cursor_sharing string
TVDCDB1.PDB01 SQL> alter system reset cursor_sharing scope=both;
System altered.
TVDCDB1.PDB01 SQL> show parameter cursor_sharing
NAME TYPE VALUE
---------------- ----------- ----------------
cursor_sharing string EXACT
PDB_SPFILE$ marks row as “deleted” on parameter reset
[EDIT: 03.01.2022, next paragraphs corrected followed helpful comments from Martin Albert Berger]
When going back to the CDB root, I would expect to not see anymore the parameter on PDB_SPFILE$ view. Well, it is still there:
SQL> connect / as sysdba
Connected.
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> select pdb_name,name,value$ from pdb_spfile$ left join dba_pdbs on (CON_UID=pdb_uid) where name='cursor_sharing' order by name;
PDB_NAME NAME VALUE$
---------- ------------------------- --------------------
PDB01 cursor_sharing 'FORCE'
1 row selected.
SQL> -- Need to use "and bitand(nvl(spare2,0),1)=0", as row is just marked "deleted"
SQL> select pdb_name,name,value$ from pdb_spfile$ left join dba_pdbs on (CON_UID=pdb_uid) where name='cursor_sharing' and bitand(nvl(spare2,0),1)=0 order by name;
no rows selected
Use ISMODIFIED column on V$SYSTEM_PARAMETER
On V$SYSTEM_PARAMETER the “ISDEFAULT=’FALSE'” will show the SEED non-default parameters for all PDBs, even if these are not specified on the PDB pseudo-spfile:
SQL> create pluggable database pdb02 admin user pdb_admin identified by pdb_admin file_name_convert=('pdbseed','pdb02');
Pluggable database created.
SQL> alter pluggable database pdb02 open;
Pluggable database altered.
select a.pdb_name, a.name, a.value PDB_VALUE, b.value ROOT_VALUE from
(select pdb_name,name,value
from v$system_parameter a left join dba_pdbs b on (a.CON_ID=b.pdb_id)
where a.con_id>2 and isdefault='FALSE') a,
(select 'CDB$ROOT' pdb_name,name,value
from v$system_parameter where con_id=0) b
where a.name=b.name and a.pdb_name='PDB02'
order by 1,2;
PDB_NAME NAME PDB_VALUE ROOT_VALUE
---------- -------------------- -------------------- --------------------
PDB02 db_securefile PREFERRED PREFERRED
PDB02 sga_target 0 2634022912
PDB02 shared_pool_size 0 637534208
PDB02 undo_tablespace UNDOTBS1 UNDOTBS1
4 rows selected.
SQL> select 'PDB$SEED' pdb_name, name,value from v$system_parameter where con_id=2 and isdefault='FALSE';
PDB_NAME NAME VALUE
---------- -------------------- --------------------
PDB$SEED shared_pool_size 0
PDB$SEED sga_target 0
PDB$SEED undo_tablespace
PDB$SEED db_securefile PREFERRED
4 rows selected.
One needs to use the ISMODIFIED=’MODIFIED’ to get the modified values on a specific PDB:
SQL> select a.pdb_name, a.name, a.value PDB_VALUE, b.value ROOT_VALUE from
(select pdb_name,name,value
from v$system_parameter a left join dba_pdbs b on (a.CON_ID=b.pdb_id)
where a.con_id>2 and isdefault='FALSE') a,
(select 'CDB$ROOT' pdb_name,name,value
from v$system_parameter where con_id=0) b
where a.name=b.name and a.pdb_name='PDB02'
order by 1,2;
no rows selected
SQL> alter session set container=PDB01;
Session altered.
TVDCDB1.PDB01 SQL> alter system set cursor_sharing=FORCE;
System altered.
TVDCDB1.PDB01 SQL> alter session set container=CDB$ROOT;
Session altered.
SQL> select a.pdb_name, a.name, a.value PDB_VALUE, b.value ROOT_VALUE from
(select pdb_name,name,value
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') a,
(select 'CDB$ROOT' pdb_name,name,value
from v$system_parameter where con_id=0) b
where a.name=b.name and a.pdb_name='PDB02'
order by 1,2;
PDB_NAME NAME PDB_VALUE ROOT_VALUE
---------- -------------------- -------------------- --------------------
PDB01 cursor_sharing FORCE EXACT
Summary: Make sure you use the correct filter on V$SYSTEM_PARAMETER or PDB_SPFILE$ to have the parameters of a PDB.
I did a quick&dirty test:
when I set cursor_sharing in my PDB, the column pdb_spfile$.SPARE2 is 0,
when I RESET cursor_sharing, pdb_spfile$.SPARE2 changes to 1.
hth
Hi Martin,
Thanks for the comment. So I would believe the right way to use pdb_spfile$ would be:
select pdb_name,name,value$
from pdb_spfile$ left join dba_pdbs on (CON_UID=pdb_uid)
where name=’cursor_sharing’
and spare2=0
order by name;
Do you agree?
Hi Miguel,
Yes, kind of 😉
I’d use the filter
bitand(nvl(spare2,0),1)=0
as Oracle is also using it internally.
I’d prefer using something like CONTAINERS(v$spparameterfile) but I failed in my sandbox for unknown reasons.