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

42 Administering PDBs with SQL*Plus

42.4 Using the ALTER SYSTEM Statement to Modify a PDB

SQL*Plus® User's Guide and Reference 12.1

Oracle® 12.1 Database Reference

Part I Initialization Parameters

DDL_LOCK_TIMEOUT

Part III Dynamic Performance Views

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:

NAME	DESCRIPTION
O7_DICTIONARY_ACCESSIBILITY	Version 7 Dictionary Accessibility Support
asm_diskstring	disk set locations for discovery
cell_offload_compaction	Cell packet compaction strategy
cell_offload_decryption	enable SQL processing offload of encrypted data to cells
cell_offload_parameters	Additional cell offload parameters
cell_offload_plan_display	Cell offload explain plan display
cell_offload_processing	enable SQL processing offload to cells
cell_offloadgroup_name	Set the offload group name
commit_logging	transaction commit log write behaviour
commit_wait	transaction commit log wait behaviour
commit_write	transaction commit log write behaviour
create_stored_outlines	create stored outlines for DML statements
cursor_bind_capture_destination	Allowed destination for captured bind variables
cursor_sharing	cursor sharing mode
db_block_checking	header checking and data and index block checking
db_create_file_dest	default database location
db_create_online_log_dest_1	online log/controlfile destination #1
db_create_online_log_dest_2	online log/controlfile destination #2
db_create_online_log_dest_3	online log/controlfile destination #3
db_create_online_log_dest_4	online log/controlfile destination #4
db_create_online_log_dest_5	online log/controlfile destination #5
db_file_multiblock_read_count	db block to be read each IO
db_index_compression_inheritance	options for table or tablespace level compression inheritance
db_performance_profile	Database performance category
db_securefile	permit securefile storage during lob creation
db_unrecoverable_scn_tracking	Track nologging SCN in controlfile
ddl_lock_timeout	timeout to restrict the time that ddls wait for dml lock
deferred_segment_creation	defer segment creation to first insert
dst_upgrade_insert_conv	Enables/Disables internal conversions during DST upgrade
enable_ddl_logging	enable ddl logging
exclude_seed_cdb_view	exclude PDB$SEED from CDB View Result
fixed_date	fixed SYSDATE value
global_names	enforce that database links have same name as remote database
heat_map	ILM Heatmap Tracking
inmemory_clause_default	Default in-memory clause for new tables
inmemory_force	Force tables to be in-memory or not
inmemory_query	Specifies whether in-memory queries are allowed
inmemory_size	size in bytes of in-memory area
java_jit_enabled	Java VM JIT enabled
job_queue_processes	maximum number of job queue slave processes
listener_networks	listener registration networks
log_archive_dest_1	archival destination #1 text string
log_archive_dest_10	archival destination #10 text string
log_archive_dest_11	archival destination #11 text string
log_archive_dest_12	archival destination #12 text string
log_archive_dest_13	archival destination #13 text string
log_archive_dest_14	archival destination #14 text string
log_archive_dest_15	archival destination #15 text string
log_archive_dest_16	archival destination #16 text string
log_archive_dest_17	archival destination #17 text string
log_archive_dest_18	archival destination #18 text string
log_archive_dest_19	archival destination #19 text string
log_archive_dest_2	archival destination #2 text string
log_archive_dest_20	archival destination #20 text string
log_archive_dest_21	archival destination #21 text string
log_archive_dest_22	archival destination #22 text string
log_archive_dest_23	archival destination #23 text string
log_archive_dest_24	archival destination #24 text string
log_archive_dest_25	archival destination #25 text string
log_archive_dest_26	archival destination #26 text string
log_archive_dest_27	archival destination #27 text string
log_archive_dest_28	archival destination #28 text string
log_archive_dest_29	archival destination #29 text string
log_archive_dest_3	archival destination #3 text string
log_archive_dest_30	archival destination #30 text string
log_archive_dest_31	archival destination #31 text string
log_archive_dest_4	archival destination #4 text string
log_archive_dest_5	archival destination #5 text string
log_archive_dest_6	archival destination #6 text string
log_archive_dest_7	archival destination #7 text string
log_archive_dest_8	archival destination #8 text string
log_archive_dest_9	archival destination #9 text string
log_archive_dest_state_1	archival destination #1 state text string
log_archive_dest_state_10	archival destination #10 state text string
log_archive_dest_state_11	archival destination #11 state text string
log_archive_dest_state_12	archival destination #12 state text string
log_archive_dest_state_13	archival destination #13 state text string
log_archive_dest_state_14	archival destination #14 state text string
log_archive_dest_state_15	archival destination #15 state text string
log_archive_dest_state_16	archival destination #16 state text string
log_archive_dest_state_17	archival destination #17 state text string
log_archive_dest_state_18	archival destination #18 state text string
log_archive_dest_state_19	archival destination #19 state text string
log_archive_dest_state_2	archival destination #2 state text string
log_archive_dest_state_20	archival destination #20 state text string
log_archive_dest_state_21	archival destination #21 state text string
log_archive_dest_state_22	archival destination #22 state text string
log_archive_dest_state_23	archival destination #23 state text string
log_archive_dest_state_24	archival destination #24 state text string
log_archive_dest_state_25	archival destination #25 state text string
log_archive_dest_state_26	archival destination #26 state text string
log_archive_dest_state_27	archival destination #27 state text string
log_archive_dest_state_28	archival destination #28 state text string
log_archive_dest_state_29	archival destination #29 state text string
log_archive_dest_state_3	archival destination #3 state text string
log_archive_dest_state_30	archival destination #30 state text string
log_archive_dest_state_31	archival destination #31 state text string
log_archive_dest_state_4	archival destination #4 state text string
log_archive_dest_state_5	archival destination #5 state text string
log_archive_dest_state_6	archival destination #6 state text string
log_archive_dest_state_7	archival destination #7 state text string
log_archive_dest_state_8	archival destination #8 state text string
log_archive_dest_state_9	archival destination #9 state text string
log_archive_min_succeed_dest	minimum number of archive destinations that must succeed
max_dump_file_size	Maximum size (in bytes) of dump file
max_string_size	controls maximum size of VARCHAR2, NVARCHAR2, and RAW types in SQL
nls_calendar	NLS calendar system name
nls_comp	NLS comparison
nls_currency	NLS local currency symbol
nls_date_format	NLS Oracle date format
nls_date_language	NLS date language name
nls_dual_currency	Dual currency symbol
nls_iso_currency	NLS ISO currency territory name
nls_language	NLS language name
nls_length_semantics	create columns using byte or char semantics by default
nls_nchar_conv_excp	NLS raise an exception instead of allowing implicit conversion
nls_numeric_characters	NLS numeric characters
nls_sort	NLS linguistic definition name
nls_territory	NLS territory name
nls_time_format	time format
nls_time_tz_format	time with timezone format
nls_timestamp_format	time stamp format
nls_timestamp_tz_format	timestamp with timezone format
object_cache_max_size_percent	percentage of maximum size over optimal of the user session's object cache
object_cache_optimal_size	optimal size of the user session's object cache in bytes
olap_page_pool_size	size of the olap page pool in bytes
open_cursors	max # cursors per session
optimizer_adaptive_features	controls adaptive features
optimizer_adaptive_reporting_only	use reporting-only mode for adaptive optimizations
optimizer_capture_sql_plan_baselines	automatic capture of SQL plan baselines for repeatable statements
optimizer_dynamic_sampling	optimizer dynamic sampling
optimizer_features_enable	optimizer plan compatibility parameter
optimizer_index_caching	optimizer percent index caching
optimizer_index_cost_adj	optimizer index cost adjustment
optimizer_inmemory_aware	optimizer in-memory columnar awareness
optimizer_mode	optimizer mode
optimizer_secure_view_merging	optimizer secure view merging and predicate pushdown/movearound
optimizer_use_invisible_indexes	Usage of invisible indexes (TRUE/FALSE)
optimizer_use_pending_statistics	Control whether to use optimizer pending statistics
optimizer_use_sql_plan_baselines	use of SQL plan baselines for captured sql statements
parallel_degree_level	adjust the computed degree in percentage
parallel_degree_limit	limit placed on degree of parallelism
parallel_degree_policy	policy used to compute the degree of parallelism (MANUAL/LIMITED/AUTO/ADAPTIVE)
parallel_force_local	force single instance execution
parallel_instance_group	instance group to use for all parallel operations
parallel_io_cap_enabled	enable capping DOP by IO bandwidth
parallel_min_time_threshold	threshold above which a plan is a candidate for parallelization (in seconds)
pdb_file_name_convert	PDB file name convert patterns and strings for create cdb/pdb
pdb_lockdown	pluggable database lockdown profile
pdb_os_credential	pluggable database OS credential to bind
plscope_settings	plscope_settings controls the compile time collection, cross reference, and storage of PL/SQL source code identifier data
plsql_ccflags	PL/SQL ccflags
plsql_code_type	PL/SQL code-type
plsql_debug	PL/SQL debug
plsql_optimize_level	PL/SQL optimize level
plsql_v2_compatibility	PL/SQL version 2.x compatibility flag
plsql_warnings	PL/SQL compiler warnings settings
query_rewrite_enabled	allow rewrite of queries using materialized views if enabled
query_rewrite_integrity	perform rewrite using materialized views with desired integrity
recyclebin	recyclebin processing
remote_dependencies_mode	remote-procedure-call dependencies mode parameter
resource_limit	master switch for resource limit
resource_manager_plan	resource mgr top plan
result_cache_mode	result cache operator usage mode
result_cache_remote_expiration	maximum life time (min) for any result using a remote object
resumable_timeout	set resumable_timeout
sessions	user and system sessions
shared_servers	number of shared servers to start up
skip_unusable_indexes	skip unusable indexes if set to TRUE
smtp_out_server	utl_smtp server and port configuration parameter
sort_area_retained_size	size of in-memory sort work area retained between fetch calls
sort_area_size	size of in-memory sort work area
spatial_vector_acceleration	enable spatial vector acceleration
sql_trace	enable SQL trace
sqltune_category	Category qualifier for applying hintsets
star_transformation_enabled	enable the use of star transformation
statistics_level	statistics level
temp_undo_enabled	is temporary undo enabled
timed_os_statistics	internal os statistic gathering interval in seconds
timed_statistics	maintain internal timing statistics
workarea_size_policy	policy used to size SQL working areas (MANUAL/AUTO)
xml_db_events	are XML DB events enabled

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:

CONTAINER_NAME	PARAMETER_NAME	PARAMETER_DESCRIPTION	PARAMETER_VALUE
CDB$ROOT	db_create_file_dest	default database location	/opt/app/oracle/oradata
CDB$ROOT	db_create_online_log_dest_1	online log/controlfile destination #1	/opt/app/oracle/oradata
CDB$ROOT	db_create_online_log_dest_2	online log/controlfile destination #2	/opt/app/oracle/fast_recovery_area
CDB$ROOT	db_securefile	permit securefile storage during lob creation	PREFERRED
CDB$ROOT	log_archive_dest_1	archival destination #1 text string	LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=jar_padstow
CDB$ROOT	log_archive_dest_2	archival destination #2 text string	SERVICE=jar_botany ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=jar_botany
CDB$ROOT	open_cursors	max # cursors per session	300
CDB$ROOT	resource_manager_plan	resource mgr top plan	SCHEDULER[0x4448]:DEFAULT_MAINTENANCE_PLAN

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:

startup

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:

startup

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