Oracle Multitenant (PDB Level) Initialization Parameters

Oracle Multitenant (PDB Level) Initialization Parameters 

I was asked what are those parameters that can be modifiable at PDB level. And my simple answer is to check v$parameters view and look for ISPDB_MODIFIABLE column have TRUE value than it is modifiable otherwise not.

I have just extract the same here, some of you might not have DBA privileges, specially developers don't have rights to view v$ views.

SrNo.	Init Parameters	Description	Default Value
1	_optimizer_gather_stats_on_conventional_dml	optimizer online stats gathering for conventional DML	TRUE
2	_optimizer_use_stats_on_conventional_dml	use optimizer statistics gathered for conventional DML	TRUE
3	_serial_direct_read	enable direct read in serial	auto
4	adg_account_info_tracking	ADG user account info tracked in standby(LOCAL) or in Primary(GLOBAL)	LOCAL
5	allow_rowid_column_type	Allow creation of rowid column	FALSE
6	approx_for_aggregation	Replace exact aggregation with approximate aggregation	FALSE
7	approx_for_count_distinct	Replace count distinct with approx_count_distinct	FALSE
8	approx_for_percentile	Replace percentile_* with approx_percentile	none
9	aq_tm_processes	number of AQ Time Managers to start	1
10	asm_diskstring	disk set locations for discovery
11	awr_pdb_autoflush_enabled	Enable/Disable AWR automatic PDB flushing	FALSE
12	bitmap_merge_area_size	maximum memory allow for BITMAP MERGE	1048576
13	blank_trimming	blank trimming semantics parameter	FALSE
14	cell_offload_compaction	Cell packet compaction strategy	ADAPTIVE
15	cell_offload_decryption	enable SQL processing offload of encrypted data to cells	TRUE
16	cell_offload_parameters	Additional cell offload parameters	NONE
17	cell_offload_plan_display	Cell offload explain plan display	AUTO
18	cell_offload_processing	enable SQL processing offload to cells	TRUE
19	cell_offloadgroup_name	Set the offload group name
20	client_result_cache_lag	client result cache maximum lag in milliseconds	3000
21	client_result_cache_size	client result cache max size in bytes	0
22	client_statistics_level	Client Statistics Level	TYPICAL
23	commit_logging	transaction commit log write behaviour
24	commit_point_strength	Bias this node has toward not preparing in a two-phase commit	1
25	commit_wait	transaction commit log wait behaviour
26	commit_write	transaction commit log write behaviour
27	common_user_prefix	Enforce restriction on a prefix of a Common User/Role/Profile name	NONE
28	container_data	which containers should data be returned from?	ALL
29	containers_parallel_degree	Parallel degree for a CONTAINERS() query	65535
30	cpu_count	maximum number of CPUs	0
31	cpu_min_count	minimum number of CPUs required
32	create_bitmap_area_size	size of create bitmap buffer for bitmap index	8388608
33	create_stored_outlines	create stored outlines for DML statements
34	cursor_bind_capture_destination	Allowed destination for captured bind variables	memory+disk
35	cursor_invalidation	default for DDL cursor invalidation semantics	IMMEDIATE
36	cursor_sharing	cursor sharing mode	EXACT
37	db_block_checking	header checking and data and index block checking	FALSE
38	db_cache_size	Size of DEFAULT buffer pool for standard block size buffers	0
39	db_create_file_dest	default database location	NONE
40	db_create_online_log_dest_1	online log/controlfile destination #1	NONE
41	db_create_online_log_dest_2	online log/controlfile destination #2	NONE
42	db_create_online_log_dest_3	online log/controlfile destination #3	NONE
43	db_create_online_log_dest_4	online log/controlfile destination #4	NONE
44	db_create_online_log_dest_5	online log/controlfile destination #5	NONE
45	db_domain	directory part of global database name stored with CREATE DATABASE
46	db_file_multiblock_read_count	db block to be read each IO	0
47	db_files	max allowable # db files	200
48	db_index_compression_inheritance	options for table or tablespace level compression inheritance	NONE
49	db_performance_profile	Database performance category
50	db_securefile	permit securefile storage during lob creation	PERMITTED
51	db_unrecoverable_scn_tracking	Track nologging SCN in controlfile	TRUE
52	ddl_lock_timeout	timeout to restrict the time that ddls wait for dml lock	0
53	default_sharing	Default sharing clause	metadata
54	deferred_segment_creation	defer segment creation to first insert	TRUE
55	dst_upgrade_insert_conv	Enables/Disables internal conversions during DST upgrade	TRUE
56	enable_automatic_maintenance_pdb	Enable/Disable Automated Maintenance for Non-Root PDB	TRUE
57	enable_ddl_logging	enable ddl logging	FALSE
58	encrypt_new_tablespaces	whether to encrypt newly created tablespaces	CLOUD_ONLY
59	fixed_date	fixed SYSDATE value	NONE
60	forward_listener	forward listener
61	global_names	enforce that database links have same name as remote database	FALSE
62	heat_map	ILM Heatmap Tracking	OFF
63	ignore_session_set_param_errors	Ignore errors during alter session param set
64	inmemory_automatic_level	Enable Automatic In-Memory management	OFF
65	inmemory_clause_default	Default in-memory clause for new tables	NONE
66	inmemory_expressions_usage	Controls which In-Memory Expressions are populated in-memory	ENABLE
67	inmemory_force	Force tables to be in-memory or not	DEFAULT
68	inmemory_optimized_arithmetic	Controls whether or not DSBs are stored in-memory	DISABLE
69	inmemory_prefer_xmem_memcompress	Prefer to store tables with given memcompress levels in xmem	NONE
70	inmemory_prefer_xmem_priority	Prefer to store tables with given priority levels in xmem	NONE
71	inmemory_query	Specifies whether in-memory queries are allowed	ENABLE
72	inmemory_size	size in bytes of in-memory area	0
73	inmemory_virtual_columns	Controls which user-defined virtual columns are stored in-memory	MANUAL
74	inmemory_xmem_size	size in bytes of in-memory xmem area	0
75	java_jit_enabled	Java VM JIT enabled	TRUE
76	job_queue_processes	maximum number of job queue slave processes	4000
77	ldap_directory_access	RDBMS's LDAP access option	NONE
78	ldap_directory_sysauth	OID usage parameter	no
79	listener_networks	listener registration networks
80	lob_signature_enable	enable lob signature	FALSE
81	local_listener	local listener
82	log_archive_min_succeed_dest	minimum number of archive destinations that must succeed	1
83	long_module_action	Use longer module and action	TRUE
84	max_datapump_jobs_per_pdb	maximum number of concurrent Data Pump Jobs per PDB	100
85	max_datapump_parallel_per_job	maximum number of parallel processes per Data Pump Job	50
86	max_dump_file_size	Maximum size (in bytes) of dump file	unlimited
87	max_idle_blocker_time	maximum idle time for a blocking session in minutes	0
88	max_idle_time	maximum session idle time in minutes	0
89	max_iops	MAX IO per second	0
90	max_mbps	MAX MB per second	0
91	max_pdbs	max number of pdbs allowed in CDB or Application ROOT	4098
92	max_string_size	controls maximum size of VARCHAR2, NVARCHAR2, and RAW types in SQL	STANDARD
93	multishard_query_data_consistency	consistency setting for multishard queries	strong
94	multishard_query_partial_results	enable partial results for multishard queries	not allowed
95	nls_calendar	NLS calendar system name	NONE
96	nls_comp	NLS comparison	BINARY
97	nls_currency	NLS local currency symbol	NONE
98	nls_date_format	NLS Oracle date format	NONE
99	nls_date_language	NLS date language name	NONE
100	nls_dual_currency	Dual currency symbol	NONE
101	nls_iso_currency	NLS ISO currency territory name	NONE
102	nls_language	NLS language name	AMERICAN
103	nls_length_semantics	create columns using byte or char semantics by default	BYTE
104	nls_nchar_conv_excp	NLS raise an exception instead of allowing implicit conversion	FALSE
105	nls_numeric_characters	NLS numeric characters	NONE
106	nls_sort	NLS linguistic definition name	NONE
107	nls_territory	NLS territory name	AMERICA
108	nls_time_format	time format	NONE
109	nls_time_tz_format	time with timezone format	NONE
110	nls_timestamp_format	time stamp format	NONE
111	nls_timestamp_tz_format	timestamp with timezone format	NONE
112	object_cache_max_size_percent	percentage of maximum size over optimal of the user session's object cache	10
113	object_cache_optimal_size	optimal size of the user session's object cache in bytes	10240000
114	olap_page_pool_size	size of the olap page pool in bytes	0
115	open_cursors	max # cursors per session	50
116	open_links	max # open links per session	4
117	optimizer_adaptive_plans	controls all types of adaptive plans	TRUE
118	optimizer_adaptive_reporting_only	use reporting-only mode for adaptive optimizations	FALSE
119	optimizer_adaptive_statistics	controls all types of adaptive statistics	FALSE
120	optimizer_capture_sql_plan_baselines	automatic capture of SQL plan baselines for repeatable statements	FALSE
121	optimizer_dynamic_sampling	optimizer dynamic sampling	2
122	optimizer_features_enable	optimizer plan compatibility parameter	19.1.0
123	optimizer_ignore_hints	enables the embedded hints to be ignored	FALSE
124	optimizer_ignore_parallel_hints	enables embedded parallel hints to be ignored	FALSE
125	optimizer_index_caching	optimizer percent index caching	0
126	optimizer_index_cost_adj	optimizer index cost adjustment	100
127	optimizer_inmemory_aware	optimizer in-memory columnar awareness	TRUE
128	optimizer_mode	optimizer mode	all_rows
129	optimizer_secure_view_merging	optimizer secure view merging and predicate pushdown/movearound	TRUE
130	optimizer_use_invisible_indexes	Usage of invisible indexes (TRUE/FALSE)	FALSE
131	optimizer_use_pending_statistics	Control whether to use optimizer pending statistics	FALSE
132	optimizer_use_sql_plan_baselines	use of SQL plan baselines for captured sql statements	TRUE
133	parallel_degree_limit	limit placed on degree of parallelism	CPU
134	parallel_degree_policy	policy used to compute the degree of parallelism (MANUAL/LIMITED/AUTO/ADAPTIVE)	MANUAL
135	parallel_force_local	force single instance execution	FALSE
136	parallel_instance_group	instance group to use for all parallel operations	NONE
137	parallel_max_servers	maximum parallel query servers per instance
138	parallel_min_degree	controls the minimum DOP computed by Auto DOP	1
139	parallel_min_time_threshold	threshold above which a plan is a candidate for parallelization (in seconds)	AUTO
140	parallel_servers_target	instance target in terms of number of parallel servers	0
141	pdb_file_name_convert	PDB file name convert patterns and strings for create cdb/pdb
142	pdb_lockdown	pluggable database lockdown profile
143	pdb_os_credential	pluggable database OS credential to bind
144	pdb_template	PDB template
145	pga_aggregate_limit	limit of aggregate PGA memory for the instance or PDB	0
146	pga_aggregate_target	Target size for the aggregate PGA memory consumed by the instance	0
147	plscope_settings	plscope_settings controls the compile time collection, cross reference, and stor age of PL/SQL source code identifier and SQL statement data	IDENTIFIERS:NONE
148	plsql_ccflags	PL/SQL ccflags	NONE
149	plsql_code_type	PL/SQL code-type	INTERPRETED
150	plsql_debug	PL/SQL debug	FALSE
151	plsql_optimize_level	PL/SQL optimize level	2
152	plsql_v2_compatibility	PL/SQL version 2.x compatibility flag	FALSE
153	plsql_warnings	PL/SQL compiler warnings settings	NONE
154	private_temp_table_prefix	Private temporary table prefix	ORA$PTT_
155	query_rewrite_enabled	allow rewrite of queries using materialized views if enabled	TRUE
156	query_rewrite_integrity	perform rewrite using materialized views with desired integrity	enforced
157	recyclebin	recyclebin processing	on
158	remote_dependencies_mode	remote-procedure-call dependencies mode parameter	timestamp
159	remote_listener	remote listener
160	remote_recovery_file_dest	default remote database recovery file location for refresh/relocate	NONE
161	resource_limit	master switch for resource limit	TRUE
162	resource_manager_plan	resource mgr top plan
163	result_cache_max_result	maximum result size as percent of cache size	5
164	result_cache_max_size	maximum amount of memory to be used by the cache	1
165	result_cache_mode	result cache operator usage mode	MANUAL
166	result_cache_remote_expiration	maximum life time (min) for any result using a remote object	0
167	resumable_timeout	set resumable_timeout	0
168	rollback_segments	undo segment list
169	scheduler_follow_pdbtz	Make scheduler objects follow PDB TZ	FALSE
170	session_cached_cursors	Number of cursors to cache in a session.	50
171	sessions	user and system sessions
172	sga_min_size	Minimum, guaranteed size of PDB's SGA	0
173	sga_target	Target size of SGA	0
174	shadow_core_dump	Core Size for Shadow Processes	partial
175	shared_pool_size	size in bytes of shared pool	134217728
176	shared_servers	number of shared servers to start up	4294967294
177	shrd_dupl_table_refresh_rate	duplicated table refresh rate (in seconds)	60
178	skip_unusable_indexes	skip unusable indexes if set to TRUE	TRUE
179	smtp_out_server	utl_smtp server and port configuration parameter
180	sort_area_retained_size	size of in-memory sort work area retained between fetch calls	0
181	sort_area_size	size of in-memory sort work area	65536
182	spatial_vector_acceleration	enable spatial vector acceleration	FALSE
183	sql_trace	enable SQL trace	FALSE
184	sql92_security	require select privilege for searched update/delete	TRUE
185	sqltune_category	Category qualifier for applying hintsets	DEFAULT
186	star_transformation_enabled	enable the use of star transformation	FALSE
187	statistics_level	statistics level	TYPICAL
188	tde_configuration	Per-PDB configuration for Transparent Data Encryption	NONE
189	temp_undo_enabled	is temporary undo enabled	FALSE
190	timed_os_statistics	internal os statistic gathering interval in seconds	0
191	timed_statistics	maintain internal timing statistics	FALSE
192	undo_management	instance runs in SMU mode if TRUE, else in RBU mode	AUTO
193	undo_retention	undo retention in seconds	900
194	undo_tablespace	use/switch undo tablespace	NONE
195	unified_audit_systemlog	Syslog facility and level for Unified Audit
196	workarea_size_policy	policy used to size SQL working areas (MANUAL/AUTO)	AUTO
197	xml_db_events	are XML DB events enabled	enable

I hope, above will help you to understand what you can change at PDB level.

Thanks & Regards,

Chandan Tanwani

Oracle Performance Tuning Certified Expert