[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.
col pdb_name for a10
col value for a20
col pdb_value for a20
col root_value for a20
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
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 order by name;
The V$SYSTEM_PARAMETER is well documented, while the PDB_SPFILE$ is not.
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, then BOTH is the default. If a parameter file was used to start up the database, then MEMORY is 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$ not updated on parameter reset
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.
And this remains even after restart the PDB or the complete instance.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 2634018904 bytes
Fixed Size 9138264 bytes
Variable Size 1526726656 bytes
Database Buffers 822083584 bytes
Redo Buffers 7634944 bytes
In-Memory Area 268435456 bytes
Database mounted.
Database opened.
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.
And also after unplug and plug the PDB back!
SQL> alter pluggable database pdb01 close;
Pluggable database altered.
SQL> alter pluggable database pdb01 unplug into '/tmp/pdb01.xml';
Pluggable database altered.
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> drop pluggable database pdb01 keep datafiles;
Pluggable database dropped.
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;
no rows selected
SQL> create pluggable database pdb01 using '/tmp/pdb01.xml' nocopy;
Pluggable database created.
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> alter pluggable database pdb01 open;
Pluggable database altered.
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.
But the value is not used on the PDB itself:
SQL> alter session set container=pdb01;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
PDB01
SQL> show spparameter cursor_sharing
SID NAME TYPE VALUE
-------- ---------------- ----------- ----------------
* cursor_sharing string
SQL> show parameter cursor_sharing
NAME TYPE VALUE
---------------- ----------- ----------------
cursor_sharing string EXACT
Default values shown on V$SYSTEM_PARAMETER
The “annoying” part of the using the V$SYSTEM_PARAMETER is that it will show always the SEED non-default parameters, 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.
Summary: I would prefer to use the PDB_SPFILE$, as it tries to imitate a real SPFILE (at least name-wise), but seems it is not really working.
