PDB-modifiable Oracle Database Parameters

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.