Check the Oracle Database size CDB and PDB

      No Comments on Check the Oracle Database size CDB and PDB

Following Queries for NON-CDB database:
Check the database physical consumed size

select sum(bytes)/1024/1024/1024 size_in_gb from dba_data_files;

Total space used by the data in the database.

select sum(bytes)/1024/1024/1024 size_in_gb from dba_segments;

Check the size of the User or Schema in Oracle database.

select owner, sum(bytes)/1024/1024/1024 Size_GB from dba_segments group by owner;

Find free space and used space in the Oracle database.

"Reserved_Space(MB)", "Reserved_Space(MB)" - "Free_Space(MB)" "Used_Space(MB)","Free_Space(MB)"
(select sum(bytes/(10141024)) from dba_data_files) "Reserved_Space(MB)", (select sum(bytes/(10241024)) from dba_free_space) "Free_Space(MB)"
from dual );

Check overall size of database,temp and redo file.

( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +
( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB"

Queries for CDB and PDB databases-

Check the PDB Size of the databases

select con_id, name, open_mode, total_size/1024/1024/1024 "PDB_SIZE_GB" from v$pdbs;

Check the CDB Size of the databases

select sum(bytes)/1024/1024/1024 from cdb_data_files;

Leave a Reply

Your email address will not be published. Required fields are marked *