Change instance parameters for a CDB/PDB
Overview
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.
References
- Database Administrator’s Guide
- SQL*Plus® User's Guide and Reference 12.1
- Oracle® 12.1 Database Reference
- Oracle® 12.1 SQL Language Reference
Reading Notes
"ALTER SYSTEM" says:
If you are connected to a CDB:
- To alter the CDB as a whole, the current container must be the root and you must have the commonly granted ALTER SYSTEM privilege.
- To alter a PDB, the current container must be the PDB and you must have the ALTER SYSTEM privilege, either granted commonly or granted locally in the PDB.
Procedure
Find all PDB Modifiable Parameter
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.
Current Non-Default PDB Parameters
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:
Connect to PLUM PDB
Run the following SQL command to connect to the PLUM PDB:
alter session set container=plum;
The expected output is:
Session altered.
Change System Parameter in Memory Only
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
Restart PLUM PDB
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.
Verify Parameter Is Now Default
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
Set Parameter in Memory and SPFILE
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
Restart PLUM PDB Again
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.
Verify Correct Setting of Parameter
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