[20250515]删除在Pluggable Database设置的参数.txt

--//想删除在Pluggable Database设置的参数，发现无法通过以前的操作方式删除，做一个记录以及解决方法：

1.环境：
SYS@book> @ver2
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 21.0.0.0.0
BANNER                        : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY                 : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

2.问题提出:
--//Pluggable Database设置的参数保存在cdb的pdb_spfile$表中：
$ cat spfile.sql
column spare2 format 9999999
column spare1 format 9999999
column sid format a5
column VALUE$  format a20
column COMMENT$ format a30
select * from pdb_spfile$;

SYS@book01p> alter system set undo_retention = 14400;
System altered.

SYS@book> @ spfile
DB_UNIQ_NAME    PDB_UID SID   NAME                           VALUE$               COMMENT$   SPARE1   SPARE2 SPARE3
------------ ---------- ----- ------------------------------ -------------------- -------- -------- -------- ---------
*            1073777561 *     resource_manager_plan          ''                                   0        1
*            1073777561 *     temp_undo_enabled              TRUE                                 0        0
*            1073777561 *     unified_audit_systemlog        'LOCAL1.INFO'                        0        0
*            1073777561 *     undo_retention                 14400                            14400        0

SYS@book01p> show parameter undo_retention
PARAMETER_NAME TYPE    VALUE
-------------- ------- ------
undo_retention integer 14400

--//假设要删除设置的参数undo_retention。

SYS@book01p> alter system reset undo_retention ;
System altered.

SYS@book01p> show parameter undo_retention
PARAMETER_NAME TYPE    VALUE
-------------- ------- -----
undo_retention integer 14400

--//使用spfile查询发现还是在表pdb_spfile$中。这样讲删除只能武断直接执行dml操作对pdb_spfile$。

SYS@book> delete from pdb_spfile$ where PDB_UID=1073777561 and name='undo_retention';
1 row deleted.

SYS@book> commit ;
Commit complete.

--//查询一些链接：https://oracle-base.com/articles/12c/multitenant-configure-instance-parameters-of-cdb-and-pdb-12cr1

PDB_SPFILE$

As mentioned previously, initialization parameters for PDBs are stored in a system table called PDB_SPFILE$. There isn't
much information about it in the main documentation, but it is discussed in the following MOS note.

Initialization parameters in a Multitenant database - Facts and additional information (Doc ID 2101596.1)

The documentation encourages us to use the V$PARAMETER, V$SYSTEM_PARAMETER and V$SPPARAMETER views, which all include a
CON_ID column, rather than querying the PDB_SPFILE$ table.

It is not 100% clear how the PDB_SPFILE$ table is managed. For example, this MOS note says reset parameters remain in
the table, but are marked as deleted, but there doesn't seem to be anything in the row that indicates a value is marked
as deleted.

ALTER SYSTEM RESET DOESN'T UPDATE PARAM VALUE IN PDB_SPFILE$ (Doc ID 2287601.1)

Clearly there are situations where the contents of the PDB_SPFILE$ table can cause issues, and need manual intervention.
In the following MOS note, the recommendation is to manually delete a specific parameter from the table and restart the
PDB.

PDB Is Not Picking Up local_listener Parameter From Container Database (Doc ID 2699062.1)

What I'm about to discuss is not a recommendation. It is something I have done to solve a specific situation. Do this
under advice from Oracle Support, or at your own risk!

One scenario that has caused issues for me relates to creating a PDB from a non-CDB instance. At the end of the process
I've been left with PDB-level parameters in the PDB_SPFILE$ table that I don't want, and resetting them at the PDB level
doesn't seem to have any impact. To solve this I did the following.

Get the list of parameters, including the PDB_UID.

set linesize 120
column pdb_name format a10
column name format a30
column value$ format a30

select ps.db_uniq_name,
       ps.pdb_uid,
       p.name as pdb_name,
       ps.name,
       ps.value$
from   pdb_spfile$ ps
       join v$pdbs p on ps.pdb_uid = p.con_uid
order by 1, 2, 3;

Delete the PDB-level parameters from the table, using the PDB_UID value.

delete from pdb_spfile$ where pdb_uid = {your PDB_UID value};
commit;

Restart the container database.

It's rather drastic, and as I warned above, this is not a recommendation!