Setting max size of each Oracle Pluggable Database Inside same Cdb -- For capacity management

 

We had requirement  where we  want to assign maxsize  for each   Pdb for capacity  planning  whenw e had multiple Pdb iniside Same Cdb .

Luckily Oracle  Doc ID 2166477.1  came to rescue  with below Steps 

Default of MAX_PDB_STORAGE is no limit.

Run following sql in current PDB to limit the size of all datafiles of that PDB:

ALTER PLUGGABLE DATABASE STORAGE (MAXSIZE <MAXSIZE>);

To verify the setting, run below in current PDB:

select PROPERTY_VALUE FROM database_properties WHERE property_name = 'MAX_PDB_STORAGE';

To check every PDB's setting, run below in CDB:

select PROPERTY_NAME,PROPERTY_VALUE,DESCRIPTION,CON_ID FROM cdb_properties WHERE property_name = 'MAX_PDB_STORAGE';

The storage limits for PDBs (like MAX_PDB_STORAGE and MAX_SHARED_TEMP_SIZE) are also  stored in CDB_PROPERTIES.

  SELECT name,

     total_size,

     NVL(property_value, 'UNLIMITED') AS "MAX_SIZE"

 FROM v$containers c, cdb_properties p

  WHERE p.con_id(+) = c.con_id

  AND p.property_name(+) = 'MAX_PDB_STORAGE'

  /

Note : 

The MAX_SIZE column in V$PDBS reflects the original MAXSIZE set using the “ALTER PLUGGABLE DATABASE STORAGE” statement. However, this MAX_SIZE value does not include the discount percent (_pdb_max_size_discount with default 10%) which is included in all internal calculations to get the real maxsize.

To get the real MAXSIZE including the discount percent, query ACTUAL_MAX_SIZE from X$CON 

SQL> select ACTUAL_MAX_SIZE from X$CON;

SQL> SELECT total_size, max_size, (max_size + (max_size*0.10)) AS REAL_MAX_SIZE FROM v$pdbs;

We will   get below Eror ORA-65114  if we  try to add space in pdb beyond  ACTUAL_MAX_SIZE

ORA-65114: space usage in container is too high

References : 

How to set and check the max size of PLUGGABLE DATABASE STORAGE (Doc ID 2166477.1)