Dec 22, 2021
Pluggable Databases- Files
Pluggable databases are designed to be a set of files you can move from one root container database to another.
That is, we can unplug a pluggable database, and upon plugging it back into either the same root container database or some other root container database, we would have our original pluggable database back—with all of the application schemas, users, metadata, grants, data, and even our pluggable database parameter settings (settings that were not inherited from the root container).
This is achieved by storing pluggable database–specific parameter settings in a data dictionary table: SYS.PDB_ SPFILE$.
You can view parameters modifiable at the PDB level via this query:
SQL> SELECT name, value
FROM v$system_parameter
WHERE ispdb_modifiable = ‘TRUE’
ORDER BY name;
NAME VALUE
adg_account_info_tracking
allow_deprecated_rpcs
allow_rowid_column_type
approx_for_aggregation
approx_for_count_distinct
approx_for_percentile
aq_tm_processes
asm_diskstring
auto_start_pdb_services
awr_pdb_autoflush_enabled
bitmap_merge_area_size
blank_trimming
blockchain_table_max_no_drop
…
You set the PDB level parameters by using the ALTER SYSTEM … CONTAINER clause or by connecting the pluggable database and issuing the ALTER SYSTEM command:
$ sqlplus / as sysdba
SQL> alter session set container=pdb1; SQL> alter system set statistics_level=all;
It is in this fashion that pluggable databases can override a parameter setting for some parameters in an SPFILE and have those parameter settings travel with them as they move from root container database to root container database.
Parameter File Wrap-Up
In this section, we covered the basics of managing Oracle initialization parameters and parameter files.
We looked at how to set parameters, view parameter values, and have those settings persist across database restarts.
We explored the two types of database parameter files: legacy PFILEs (simple text files) and the newer SPFILEs.
For all existing databases, using SPFILEs is recommended for the ease of administration and clarity they bring.
The ability to have a single source of parameter “truth” for the database, along with the ability of the ALTER SYSTEM command to persist the parameter values, makes SPFILEs a compelling feature. I started using them the instant they became available and haven’t looked back.
More Details