Hello Friend's,
In This Post we will discuss about How to set and check the max size of PLUGGABLE DATABASE STORAGE
So let's get started
When creating a Pluggable Database (PDB) in a Container Database (CDB), you can set its maximum size limit using the STORAGE clause in the CREATE PLUGGABLE DATABASE command. If you need to adjust this limit later, you can use the ALTER PLUGGABLE DATABASE query. To alter a PDB's storage limit, you must connect to the PDB and issue the ALTER DATABASE command with the appropriate parameters. This is the only ALTER PLUGGABLE DATABASE operation that requires dedicated execution from within the PDB. The other ALTER PLUGGABLE DATABASE operations can be performed remotely without connecting to the PDB. This capability ensures backward compatibility for applications that have been migrated to a CDB environment.
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 25G);
SQL> set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a30
select INST_ID,INSTANCE_NAME, name DB_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,version DB_VERSION,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance;SQL> SQL> SQL>
INST_ID INSTANCE_NAME DB_NAME HOST_NAME DATABASE_ROLE OPEN_MODE DB_VERSION LOGINS DB UP TIME
---------- ---------------- --------- ------------------------------ ---------------- ---------- ----------------- ---------- -----------------------------
1 CDBFIN CDBFIN dm01db01.database.com PRIMARY READ WRITE 19.0.0.0.0 ALLOWED 06-APR-2024 21:35:08
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 FINDB MOUNTED
SQL> alter pluggable database FINDB open read write;
Pluggable database altered.
SQL> alter session set container=FINDB;
Session altered.
SQL> ALTER PLUGGABLE DATABASE STORAGE (MAXSIZE 25G);
Pluggable database altered.
To verify the setting, run below in current PDB:
SQL> select (PROPERTY_VALUE/1024/1024/1024) GB FROM database_properties WHERE property_name = 'MAX_PDB_STORAGE';
GB
----------
25
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';
Storage limits for Pluggable Databases (PDBs), such as MAX_PDB_STORAGE and MAX_SHARED_TEMP_SIZE, are also maintained within the central properties (CDB_PROPERTIES) of the containing Consolidated Database (CDB).
SQL> col name for a34
SQL> col MAX_SIZE for a34
SQL> 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'; 2 3 4 5 6 7
NAME TOTAL_SIZE MAX_SIZE
---------------------------------- ---------- ----------------------------------
FINDB 1380974592 26843545600
CDB$ROOT 0 UNLIMITED
PDB$SEED 1354760192 UNLIMITED
The MAX_SIZE in V$PDBS shows the initial MAXSIZE set by "ALTER PLUGGABLE DATABASE STORAGE." However, it excludes the discount rate (_pdb_max_size_discount, typically 10%). This discount is used internally to calculate the actual maximum size. To get the true MAXSIZE with the discount applied, use the ACTUAL_MAX_SIZE column from X$CON.
SELECT actual_max_size
FROM x$con;
ACTUAL_MAX_SIZE
---------------
0
0
2.9528E+10
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
---------- ---------- -------------
1354760192 0 0
1380974592 2.6844E+10 2.9528E+10
Adding space to a pluggable database (PDB) beyond its maximum size (ACTUAL_MAX_SIZE) will result in the ORA-65114 error.
This error generally comes when total size of PDB after resize of the datafile is more than the size you have allocated to a PDB while creating.
e.g :
SQL> ALTER DATABASE DATAFILE '+DATA' RESIZE 4g;
ALTER DATABASE DATAFILE '+DATA' RESIZE 4g
*
ERROR at line 1:
ORA-65114: space usage in container is too high
If you face this error you can revert the same using below steps .
You can modify the allocation storage for a Pluggable Database (PDB) and subsequently attempt to alter the size of its data file by logging on to the PDB.
SQL> alter session set container=FINDB;
Session altered.
Configure the storage allocation as either a specific value or set it to an unbounded limit.
SQL> ALTER PLUGGABLE DATABASE STORAGE UNLIMITED;
Pluggable database altered.
ConversionConversion EmoticonEmoticon