Home » Check the Size of Oracle Database and PDB database

Check the Size of Oracle Database and PDB database

by tuanlp

 

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 the data.

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

Check the size of the 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 the database.

select
"Reserved_Space(MB)", "Reserved_Space(MB)" - "Free_Space(MB)" "Used_Space(MB)","Free_Space(MB)"
from(
select
(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 temp files plus redo file.

select
( 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"
from
dual;

Following 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;

You may also like