I got an interesting question the other day: “which Oracle Database Parameters are PDB-modifiable?”, i.e., which parameters can have different values for an individual PDB than the CDB? Looking into the documentation, I realized there is no good one-stop page. Instead, the information is scattered throughout the Database Reference. Luckily, however, the answer can be retrieved from the database itself by querying the v$parameters system view.
The v$parameter system view contains, among others, a column called ispdb_modifiable which holds either the string 'TRUE' or 'FALSE'. As you have guessed by the column name and possible values, if the value is 'TRUE' for a given parameter, it can be modified for the PDB. You can write a query such as the one below (make sure you also include the isdeprecated column to filter out deprecated parameters):
SELECT name,
CASE
WHEN type = 1 THEN 'Boolean'
WHEN type = 2 THEN 'String'
WHEN type = 3 THEN 'Integer'
WHEN type = 4 THEN 'Parameter file'
WHEN type = 5 THEN 'Reserved'
WHEN type = 6 THEN 'Big integer'
END AS parameter_type,
default_value
FROM v$parameter
WHERE ispdb_modifiable = 'TRUE' AND isdeprecated = 'FALSE'
ORDER BY name;
The result of this query on, e.g., Oracle Database 19c RU 18 returns 196 of PDB-modifiable parameters:
SQL> SELECT name,
2 CASE
3 WHEN type = 1 THEN 'Boolean'
4 WHEN type = 2 THEN 'String'
5 WHEN type = 3 THEN 'Integer'
6 WHEN type = 4 THEN 'Parameter file'
7 WHEN type = 5 THEN 'Reserved'
8 WHEN type = 6 THEN 'Big integer'
9 END AS parameter_type,
10 default_value
11 FROM v$parameter
12 WHERE ispdb_modifiable = 'TRUE' AND isdeprecated = 'FALSE'
13 ORDER BY name;
NAME PARAMETER_TYPE DEFAULT_VALUE
_______________________________________ _________________ ___________________
adg_account_info_tracking String LOCAL
allow_rowid_column_type Boolean FALSE
approx_for_aggregation Boolean FALSE
approx_for_count_distinct Boolean FALSE
approx_for_percentile String none
aq_tm_processes Integer 1
asm_diskstring String
awr_pdb_autoflush_enabled Boolean FALSE
bitmap_merge_area_size Integer 1048576
blank_trimming Boolean FALSE
blockchain_table_max_no_drop Integer
cell_offload_compaction String ADAPTIVE
cell_offload_decryption Boolean TRUE
cell_offload_parameters String NONE
cell_offload_plan_display String AUTO
cell_offload_processing Boolean TRUE
cell_offloadgroup_name String
client_result_cache_lag Big integer 3000
client_result_cache_size Big integer 0
client_statistics_level String TYPICAL
commit_logging String
commit_point_strength Integer 1
commit_wait String
common_user_prefix String NONE
container_data String ALL
containers_parallel_degree Integer 65535
cpu_count Integer 0
cpu_min_count String
create_bitmap_area_size Integer 8388608
create_stored_outlines String
cursor_bind_capture_destination String memory+disk
cursor_invalidation String IMMEDIATE
cursor_sharing String EXACT
db_block_checking String FALSE
db_cache_size Big integer 0
db_create_file_dest String NONE
db_create_online_log_dest_1 String NONE
db_create_online_log_dest_2 String NONE
db_create_online_log_dest_3 String NONE
db_create_online_log_dest_4 String NONE
db_create_online_log_dest_5 String NONE
db_domain String
db_file_multiblock_read_count Integer 0
db_files Integer 200
db_index_compression_inheritance String NONE
db_performance_profile String
db_securefile String PERMITTED
db_unrecoverable_scn_tracking Boolean TRUE
ddl_lock_timeout Integer 0
default_sharing String metadata
deferred_segment_creation Boolean TRUE
dst_upgrade_insert_conv Boolean TRUE
enable_automatic_maintenance_pdb Boolean TRUE
enable_ddl_logging Boolean FALSE
encrypt_new_tablespaces String CLOUD_ONLY
fast_start_parallel_rollback String LOW
fixed_date String NONE
forward_listener String
global_names Boolean FALSE
heat_map String OFF
identity_provider_config String NONE
identity_provider_type String NONE
ignore_session_set_param_errors String
inmemory_automatic_level String OFF
inmemory_clause_default String NONE
inmemory_expressions_usage String ENABLE
inmemory_force String DEFAULT
inmemory_optimized_arithmetic String DISABLE
inmemory_prefer_xmem_memcompress String NONE
inmemory_prefer_xmem_priority String NONE
inmemory_query String ENABLE
inmemory_size Big integer 0
inmemory_virtual_columns String MANUAL
inmemory_xmem_size Big integer 0
java_jit_enabled Boolean TRUE
job_queue_processes Integer 4000
ldap_directory_access String NONE
ldap_directory_sysauth String no
listener_networks String
lob_signature_enable Boolean FALSE
local_listener String
log_archive_min_succeed_dest Integer 1
long_module_action Boolean TRUE
max_datapump_jobs_per_pdb String 100
max_datapump_parallel_per_job String 50
max_dump_file_size String unlimited
max_idle_blocker_time Integer 0
max_idle_time Integer 0
max_iops Integer 0
max_mbps Integer 0
max_pdbs Integer 4098
max_string_size String STANDARD
multishard_query_data_consistency String strong
multishard_query_partial_results String not allowed
nls_calendar String NONE
nls_comp String BINARY
nls_currency String NONE
nls_date_format String NONE
nls_date_language String NONE
nls_dual_currency String NONE
nls_iso_currency String NONE
nls_language String AMERICAN
nls_length_semantics String BYTE
nls_nchar_conv_excp String FALSE
nls_numeric_characters String NONE
nls_sort String NONE
nls_territory String AMERICA
nls_time_format String NONE
nls_time_tz_format String NONE
nls_timestamp_format String NONE
nls_timestamp_tz_format String NONE
object_cache_max_size_percent Integer 10
object_cache_optimal_size Integer 10240000
olap_page_pool_size Big integer 0
open_cursors Integer 50
open_links Integer 4
optimizer_adaptive_plans Boolean TRUE
optimizer_adaptive_reporting_only Boolean FALSE
optimizer_adaptive_statistics Boolean FALSE
optimizer_capture_sql_plan_baselines Boolean FALSE
optimizer_dynamic_sampling Integer 2
optimizer_features_enable String 19.1.0
optimizer_ignore_hints Boolean FALSE
optimizer_ignore_parallel_hints Boolean FALSE
optimizer_index_caching Integer 0
optimizer_index_cost_adj Integer 100
optimizer_inmemory_aware Boolean TRUE
optimizer_mode String all_rows
optimizer_real_time_statistics Boolean FALSE
optimizer_secure_view_merging Boolean TRUE
optimizer_use_invisible_indexes Boolean FALSE
optimizer_use_pending_statistics Boolean FALSE
optimizer_use_sql_plan_baselines Boolean TRUE
parallel_degree_limit String CPU
parallel_degree_policy String MANUAL
parallel_force_local Boolean FALSE
parallel_instance_group String NONE
parallel_max_servers Integer
parallel_min_degree String 1
parallel_min_time_threshold String AUTO
parallel_servers_target Integer 0
pdb_file_name_convert String
pdb_lockdown String
pdb_os_credential String
pdb_template String
pga_aggregate_limit Big integer 0
pga_aggregate_target Big integer 0
plscope_settings String IDENTIFIERS:NONE
plsql_ccflags String NONE
plsql_code_type String INTERPRETED
plsql_optimize_level Integer 2
plsql_warnings String NONE
private_temp_table_prefix String ORA$PTT_
query_rewrite_enabled String true
query_rewrite_integrity String enforced
recyclebin String on
remote_dependencies_mode String timestamp
remote_listener String
remote_recovery_file_dest String NONE
resource_limit Boolean TRUE
resource_manager_plan String
result_cache_max_result Integer 5
result_cache_max_size Big integer 1
result_cache_mode String MANUAL
result_cache_remote_expiration Integer 0
resumable_timeout Integer 0
rollback_segments String
scheduler_follow_pdbtz Boolean FALSE
session_cached_cursors Integer 50
sessions Integer
sga_min_size Big integer 0
sga_target Big integer 0
shadow_core_dump String partial
shared_pool_size Big integer 134217728
shared_servers Integer 4294967294
shrd_dupl_table_refresh_rate Integer 60
skip_unusable_indexes Boolean TRUE
smtp_out_server String
sort_area_retained_size Integer 0
sort_area_size Integer 65536
spatial_vector_acceleration Boolean FALSE
sql92_security Boolean TRUE
sqltune_category String DEFAULT
star_transformation_enabled String FALSE
statistics_level String TYPICAL
tde_configuration String NONE
temp_undo_enabled Boolean FALSE
timed_os_statistics Integer 0
timed_statistics Boolean FALSE
undo_management String AUTO
undo_retention Integer 900
undo_tablespace String NONE
unified_audit_systemlog String
workarea_size_policy String AUTO
xml_db_events String enable
xml_handling_of_invalid_chars String raise_error
196 rows selected.
Photo of beautiful Hallstatt, Austria by Paul Skorupskas on Unsplash