ENABLE_AUTOMATIC_MAINTENANCE_PDB parameter in Oracle

ENABLE_AUTOMATIC_MAINTENANCE_PDB parameter will introduced in 12c for automatic maintenance task configured by Oracle will run on all PDB’s plus CDB. It is set default to TRUE means enabled for all PDB’s and CDB.

Check the value of parameter in PDBs and CDB

SQL> show parameter ENABLE_AUTOMATIC_MAINTENANCE_PDB

NAME                             TYPE    VALUE
-------------------------------- ------- -----
enable_automatic_maintenance_pdb boolean TRUE

Change the value with ALTER SYSTEM for current connected PDB or CDB

ALTER SYSTEM SET enable_automatic_maintenance_pdb=FALSE;
ALTER SYSTEM SET enable_automatic_maintenance_pdb=FALSE CONTAINER=CURRENT;


Change the value for all PDBs and CDB Databases

ALTER SYSTEM SET enable_automatic_maintenance_pdb=TRUE CONTAINER=ALL;


For changes in spfile for permanent effect

ALTER SYSTEM SET enable_automatic_maintenance_pdb=FALSE scope=both;


ALTER SYSTEM SET enable_automatic_maintenance_pdb=TRUE CONTAINER=ALL scope=both;

Note: FOR CDB Changes reflect in SPFILE present in ORACLE Home location.
For PDB connect changes found in dictionary view PDB_SPFILE$
col db_uniq_name format a10
col name format a15
col value$ format a10
select db_uniq_name, pdb_uid, name, value$ from pdb_spfile$;


Example: I change the value of CDB to root, but my PDB database is true.

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> ALTER SYSTEM SET enable_automatic_maintenance_pdb=FALSE scope=both;
System altered.
SQL>
SQL> conn hr@xepdb1
Enter password:
Connected.

SQL> show parameter enable_automatic
NAME                             TYPE    VALUE
-------------------------------- ------- -----
enable_automatic_maintenance_pdb boolean TRUE

SQL> show parameter enable_automatic
NAME                             TYPE    VALUE
-------------------------------- ------- -----
enable_automatic_maintenance_pdb boolean FALSE