How to Check CDB Size & PDB Size in Oracle Database

How to Check CDB Size & PDB Size in Oracle Database

1. Check CDB (Container Database) Size Including All Data Files, Temp Files, Redo Logs, and Control Files

Use the following query to calculate the size of the entire Container Database (CDB), including data files, temp files, redo logs, and control files:

SELECT 
 x.data_size + y.temp_size + z.redo_size + w.controlfile_size 
AS "CDB_SIZE_GB" 
FROM 
 (SELECT SUM(bytes)/1024/1024/1024 AS data_size FROM 
dba_data_files) x,
 (SELECT NVL(SUM(bytes), 0)/1024/1024/1024 AS temp_size 
FROM dba_temp_files) y,
 (SELECT SUM(bytes)/1024/1024/1024 AS redo_size FROM 
sys.v_$log) z,
 (SELECT SUM(BLOCK_SIZE * FILE_SIZE_BLKS)/1024/1024/1024 
AS controlfile_size FROM v$controlfile) w;


This query provides the total size of the CDB, summing up all essential components of the database.

---

2. Check CDB Size Including Data Files and Temp Files with the List of PDBs

To get a detailed breakdown of the CDB size along with the sizes of individual Pluggable Databases (PDBs):

COLUMN con_id FORMAT 9999
COLUMN name FORMAT A20
COLUMN open_mode FORMAT A10
COLUMN SUM(SIZE_GB) FORMAT 999.99
SET PAGESIZE 50
SET LINESIZE 80
SET FEEDBACK ON

SELECT 
    con_id,
    name,
    SUM(SIZE_GB) AS "CDB_PDB_SIZE_GB"
FROM (
    SELECT 
        c.con_id,
        NVL(p.name, 'CDB') AS name, 
        SUM(bytes)/1024/1024/1024 AS SIZE_GB 
    FROM 
        cdb_data_files c 
        LEFT JOIN v$pdbs p ON c.con_id = p.con_id
    GROUP BY c.con_id, p.name
    UNION
    SELECT 
        c.con_id,
        NVL(p.name, 'CDB') AS name, 
        SUM(bytes)/1024/1024/1024 AS SIZE_GB 
    FROM 
        cdb_temp_files c 
        LEFT JOIN v$pdbs p ON c.con_id = p.con_id
    GROUP BY c.con_id, p.name
)
GROUP BY con_id, name
ORDER BY con_id;

This query provides the size of the CDB, including PDBs, by summing up the sizes of data files and temp files.

---

3. Check Individual PDB Sizes

To get the sizes of each Pluggable Database (PDB) individually:

COLUMN con_id FORMAT 9999
COLUMN name FORMAT A20
COLUMN open_mode FORMAT A10
COLUMN pdb_size_gb FORMAT 999.99
SET PAGESIZE 50
SET LINESIZE 80
SET FEEDBACK ON

SELECT 
    con_id,
    name,
    open_mode,
    total_size / 1024 / 1024 / 1024 AS "PDB_SIZE_GB"
FROM 
    v$pdbs;

This query provides a list of all PDBs, their open modes, and their respective sizes in gigabytes.

Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment