Prevent PDB creation in a Single Tenant environment

How do I prevent the creation of a second PDB in a Single Tenant environment?

At last Oracle introduced a number of new features with 12c, but Multi Tenant is still the one outstanding innovation. But when switching to the new version my customers have one question: Do I really need this feature? Beside the not really low licensing costs for the Multi Tenant Option, there are practical use cases. But when I use one productive environment with one test environment or a consolidation into a Multi Tenant environment makes simply no sense, I still can use the Non-CDB alternative. What means using the architecture from the pre-12c versions. Unfortunately Oracle announced the deprication of Non-CDB even with release 12.1.0.2. Only the Single Tenant alternative remains for the future without any extra licensing costs. What means one CDB with only one related PDB. The problem: I am not able to prevent my keen DBA colleagues from creating an additional PDB with CREATE PLUGGABLE DATABASE. Voila, you are catched in the licence trap.

In Standard Edition you get an error when you try the same in a Single Tenant environment. Oracle offers no help in Enterprise Edition like a simple parameter. Later perhaps. When you take a closer look you will find a hidden parameter called ‘_max_pdbs’.

Unfortunately it didn’t effects anything in current Enterprise Edition releases:

PARAMETER     DESCRIPTION                                                   Session Value   Instance Value
------------  ------------------------------------------------------------- --------------- ----------------
_max_pdbs     Parameter is a hint to adjust certain attributes of the CDB   2               2

My solution looks like the following:

At first you should revoke CREATE PLUGGABLE DATABASE from all Users and Roles:

REVOKE create pluggable database FROM [any_granted_user];

The second step is to implement an event trigger, which prohibits PDB creation even for the last user (SYS):

CREATE OR REPLACE TRIGGER prevent_pdb_creation
     BEFORE CREATE ON DATABASE
DECLARE
     numcdbs NUMBER := 0;
BEGIN
     IF (ora_dict_obj_type = 'PLUGGABLE DATABASE')
     THEN
       SELECT count(*) 
       INTO numcdbs
       FROM cdb_pdbs 
       WHERE pdb_name != 'PDB$SEED' 
       AND status != 'UNPLUGGED';
       IF ( numcdbs >= 1 )
       THEN
         RAISE_APPLICATION_ERROR(-20007,'Maximum number of pluggable databases exeeded ' );
       END IF;
       ELSE
       NULL;
     END IF;
END;

I hope I could give a hand for an important problem.

Mail us or use the comments if you have any questions or suggestions.