When to use MAX_PDBS parameter

When to use MAX_PDBS parameter

March 16, 2021 0 By dianarobete

MAX_PDBS parameter was introduced in 12cR2, to limit the number of pluggable databases (PDBs) one can create in a CDB or in an application root container. Only the PDBs that are user created PDBs are counted, the PDB$SEED, Application Seed and Application Root are ignored from the count. By default this parameter’s value is 4098, which is also the maximum number of PDBs you can create in a CDB.

Why would you want to limit the number of PDBs a user can create in the CDB?

One of the reasons is licensing!

In 12c version you can have 1 user created PDB, without paying for the Multitenant option. In 19c you can have up to 3 user created PDBs in a CDB, without paying for the Multitenant option.

If you forget about this limitation and accidentally create an extra PDB, you will need to buy some extra licenses for the Multitenant option. From this perspective, you are just one CREATE statement away from making a costly mistake.

Oracle Standard Edition, will stop you from creating more than 3 PDBS (in 19c), however Enterprise Edition will not give you any warning, instead it will let you create as many PDBs as you want.

With the parameter MAX_PDBS you can limit the number of PDBs one can create. If you don’t want to pay for the Multitenant option, set this parameter to 1 in 12cR2 and 18c, and set it to 3 in 19c and up. Once the parameter is set, if you will try to create another PDB, by accident, you will receive an error: ORA-65010: maximum number of pluggable databases created.

Let’s see a quick demo:

SQL>show pdbs

    CON_ID CON_NAME        OPEN MODE  RESTRICTED
---------- --------------- ---------- ----------
         2 PDB$SEED        READ ONLY  NO
         3 TESTPDB1        READ WRITE NO
         4 TESTPDB2        READ WRITE NO
         5 TESTPDB3        READ WRITE NO

SQL>show parameter MAX_PDBS

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_pdbs                             integer     4098

SQL>alter system set MAX_PDBS=3 scope=both;

System altered.

SQL>CREATE PLUGGABLE DATABASE TESTPDB4 from TESTPDB1
   FILE_NAME_CONVERT = ('C:\ORACLE\ORADATA\TESTDB\TESTPDB1\',
                        'C:\ORACLE\ORADATA\TESTDB\TESTPDB4\')
   PATH_PREFIX = 'C:\ORACLE\ORADATA\TESTDB\TESTPDB4\';

CREATE PLUGGABLE DATABASE testpdb4 from TESTDBPDB
*
ERROR at line 1:
ORA-65010: maximum number of pluggable databases created

If you enjoyed this article, and would like to learn more about databases, please sign up to my weekly email, and you will receive my FREE guide: 7 Questions to Ask When Troubleshooting Database Performance Problems!


If you are interested in improving your Oracle Tuning skills, check out my course theultimatesqltuningformula.comFollow the link to get the best price, only $18.99 CAD available until the end of the month!