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;
bla
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.
3 Kommentare:
Hi Martin,
> 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).
It is related to container_data, e.g.:
conn / as sysdba
drop user c##test;
create user c##test identified by test;
alter user c##test set container_data=all container=current;
--alter user c##test set container_data=default container=current;
grant create session, select_catalog_role to c##test container=all;
conn c##test/test
select con_id, value from containers(v$spparameter) where name='parallel_servers_target';
CON_ID VALUE
---------- --------------------------------------------------
1
3 8
After doing:
alter user c##test set container_data=default container=current;
The last query returns:
SQL> select con_id, value from containers(v$spparameter) where name='parallel_servers_target'
CON_ID VALUE
---------- --------------------------------------------------
1
Regards,
Mikhail.
Thank you Mikahil!
When using CONTAINERS(), CONTAINER_DATA must be taken into consideration.
I should remember all the details I'm presenting at some time myself ;-)
Martin
Hi,
It's stil not clear why there are no data for other containers when performing the following SQL from the CDB$ROOT (being connected AS SYSDBA)
CDB$ROOT> select * from containers(v$spparameter);
Regards,
Dmitry
Kommentar veröffentlichen