How to Check and Set max size of PDB storage

You can specify the maximum size for any PDB database using STORAGE clause in CREATE PLUGGABLE DATABASE command. If we need to modify this then we can use the ALTER PLUGGABLE DATABASE statement to modify a pluggable database (PDB). You can perform all ALTER PLUGGABLE DATABASE tasks by connecting to a PDB and running the corresponding ALTER DATABASE statement. This functionality is provided to maintain backward compatibility for applications that have been migrated to a CDB environment. The exception is modifying PDB storage limits, for which you must use the pdb_storage_clause of ALTER PLUGGABLE DATABASE.

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

SQL> ALTER PLUGGABLE DATABASE STORAGE (MAXSIZE [MAXSIZE]);

2. To verify the setting, run below in current PDB:

SQL> select PROPERTY_VALUE FROM database_properties WHERE property_name = 'MAX_PDB_STORAGE';

3. To check every PDB’s setting, run below in CDB:

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

Default of MAX_PDB_STORAGE is no limit.

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, see below:

SQL> select ACTUAL_MAX_SIZE from X$CON;

ACTUAL_MAX_SIZE
---------------
     1476395008

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

TOTAL_SIZE   MAX_SIZE REAL_MAX_SIZE
---------- ---------- -------------
1351180288 1342177280    1476395008

SQL> select (1476395008 - 1351180288) from dual;

(1476395008-1351180288)
-----------------------
              125214720

This will fail with ORA-65114 because size of datafile will exceed ACTUAL_MAX_SIZE by 1:

SQL> create tablespace test2 datafile '/u01/app/oracle/oradata/DB199CDB/PDB199/test2.dbf' size 125214721;
create tablespace test2 datafile '/u01/app/oracle/oradata/DB199CDB/PDB199/test2.dbf' size 125214721
*
ERROR at line 1:
ORA-65114: space usage in container is too high

SQL> create tablespace test2 datafile '/u01/app/oracle/oradata/DB199CDB/PDB199/test2.dbf' size 125214720;

Tablespace created.

SQL> select TOTAL_SIZE, MAX_SIZE, (MAX_SIZE + (MAX_SIZE*0.10)) as REAL_MAX_SIZE from v$pdbs;

TOTAL_SIZE   MAX_SIZE REAL_MAX_SIZE
---------- ---------- -------------
1476395008 1342177280    1476395008

How to Check and Set max size of PDB storage

Note

Latest Posts