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

[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!