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.
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.