Check the Size of Oracle Database and PDB database

Check the Size of Oracle Database and PDB databases

Following Queries for NON-CDB database:

Check the database size physical consume on disk.

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

Check the total space used by data.

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

Check the size of User or Schema in Oracle.

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

Check free space and used space in database.

"Reserved_Space(MB)", "Reserved_Space(MB)" - "Free_Space(MB)" "Used_Space(MB)","Free_Space(MB)"
(select sum(bytes/(1014*1024)) from dba_data_files) "Reserved_Space(MB)",
(select sum(bytes/(1024*1024)) from dba_free_space) "Free_Space(MB)"
from dual );

Check overall size of database plus tempfiles plus 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"

Following Queries for CDB and PDB databases

Check the PDB Size of database

select con_id, name, open_mode, total_size from v$pdbs;

Check the CDB Size of database

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

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.