Last week I was asked by a customer if changes in a PDBs spfile parameter can be monitored easily.
My first thought was to check if AWR on CDB$ROOT level does this for me already - unfortunately it doesn't.
So the next idea was to sample PDB_SPFILE$ on my own.
Unfortunately this table is not documented by Oracle and my colleague Miguel Anjo found records there are not deleted when they are reset on PDB level.
To identify such behavior, I like to have a look in all the nice files in $ORACLE_HOME/rdbms/admin.
In e1201000.sql I luckily found this code. Even e1201000.sql is used in case of downgrade, it's quite helpful.
Rem *************************************************************************
Rem BEGIN BUG 23300354 - remove HWM and flags column values in pdb_spfile$
Rem *************************************************************************
update pdb_spfile$ set
db_uniq_name=(select sys_context('userenv', 'DB_UNIQUE_NAME') from dual)
where db_uniq_name='*';
Rem Set HWM value to 0
update pdb_spfile$ set spare1=0;
Rem Delete deleted rows and reset flags to 0
delete from pdb_spfile$ where bitand(nvl(spare2,0),1) = 1;
update pdb_spfile$ set spare2=0;
commit;
select value$ from pdb_spfile$ where name=:1 and pdb_uid=:2 and bitand(nvl(spare2,0),1)=0
I tried the probably most obvious method:
CDB$ROOT> select * from containers(v$spparameter);
But this only show results for CDB$OOT (CON_ID => 1).
Even a test with x$kspspfile shows only values for CON_ID => 1.
This is quite sad, especially as x$kspspfile IS used when I query v$spparameter from my PDB1 (which has CON_ID=3 in this test), but there seems to be some additional magic in x$kspspfile which I can not mitigate on SQL layer.
