The system parameters in the CDB are the defaults for all plugged-in PDBs. To override this, connect to the PDB, use the ALTER SYSTEM. Only a subset of system parameters can be changed this way.
"ALTER SYSTEM" says:
If you are connected to a CDB:
I ran the following SQL (see "42.4.1 About Using the ALTER SYSTEM Statement on a PDB") on the JAR CDB on PADSTOW (in the root container):
SELECT name, description FROM V$PARAMETER WHERE ispdb_modifiable = 'TRUE' ORDER BY name;
The output was:
I am surprised to find parameters related to log arrchiving to be included in this list.
I ran the following query (in the root container) to find all changed PDB-modifiable parameters:
SELECT c.name AS container_name, p.name AS parameter_name, p.description AS parameter_description, p.value AS parameter_value FROM V$PARAMETER p INNER JOIN V$CONTAINERS c USING ( con_id ) WHERE p.ispdb_modifiable = 'TRUE' AND p.isdefault = 'FALSE' ORDER BY p.name;
The result is:
Run the following SQL command to connect to the PLUM PDB:
alter session set container=plum;
The expected output is:
Session altered.
Run the following SQL command to set the system parameter in memory:
alter system set DDL_LOCK_TIMEOUT=90 scope=memory;
The expected output is:
System altered.
Confirm that the parameter has been set:
show parameter DDL_LOCK_TIMEOUT
The expected output is:
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ ddl_lock_timeout integer 90
Run the following SQL command to shut down the PLUM PDB:
shutdown immediate
The expected output is:
Pluggable Database closed.
Run the following SQL command to start up the PLUM PDB:
The expected output is:
Pluggable Database opened.
Run the following SQL command to show that the parameter was reset to the default:
show parameter DDL_LOCK_TIMEOUT
The expected output is:
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ ddl_lock_timeout integer 0
Run the following SQL command to set the parameter in memory and in the SPFILE:
alter system set DDL_LOCK_TIMEOUT=90 scope=both;
The expected output is:
System altered.
Verify that the parameter was set correctly:
show parameter DDL_LOCK_TIMEOUT
The expected output is:
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ ddl_lock_timeout integer 90
Run the following SQL command to shutdown the PLUM PDB::
shutdown immediate
The expected output is:
Pluggable Database closed.
Run the following SQL command to start the PLUM PDB:
The expected output is:
Pluggable Database opened.
Run the following SQL command to verify the correct setting of the parameter after the restart of the PDB:
show parameter DDL_LOCK_TIMEOUT
The expected output is:
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ ddl_lock_timeout integer 90