Blog

Welcome to our guide on how to check CDB size & PDB size in the Oracle database. Let’s open VirtualBox and get started!

1. Check CDB(container database) size including all datafiles, temp files, redo logs, and control files:

select x.data_size+y.temp_size+z.redo_size+w.controlfile_size "CDB_SIZE_GB" from 
(select sum(bytes)/1024/1024/1024 data_size from dba_data_files) x,
(select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files) y,
(select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log) z,
(select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) w;

2. Check CDB(container database) size including all data files and temp files with the list of PDBs(Pluggable databases):

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) from 
(select  c.con_id,nvl(p.name, 'CDB') name, sum(bytes)/1024/1024/1024 SIZE_GB from  cdb_data_files c, v$pdbs p where c.con_id=p.con_id(+) GROUP BY c.con_id,name
UNION
select  c.con_id,nvl(p.name, 'CDB') name , sum(bytes)/1024/1024/1024 SIZE_GB from  cdb_temp_files c, v$pdbs p where c.con_id=p.con_id(+) GROUP BY c.con_id,name)group by con_id,name
order by con_id;

3. Check all PDB(Pluggable database) sizes:

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;

OR,

You can connect to each PDB and run the below query:

SQL> alter session set container=PDB1;

Session altered.

SQL> select x.data_size+y.temp_size "PDB_SIZE_GB"
from (select sum(bytes)/1024/1024/1024 data_size from dba_data_files) x,
(select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files) y;

PDB_SIZE_GB
-----------
        .74

1 row selected.

I hope you found this article helpful. Feel free to reach out if you have any questions. You can check the related post Check Oracle database size.

Share on Facebook

Post on X

Follow us

Save