how to check db size in oracle

We often need to find the database size for space allocation and future growth, Let’s see how to check db size in oracle for Both the Non-CDB and CDB databases

How to check db size in oracle for Non-CDB Database

Here is the query which can be used to find the database size

select 'Total Allocated Size : ' ,sum(bytes/1024/1024/2014) , ' Gigabytes' from sys.dba_data_files
/

This includes both the SYSTEM, SYSAUX and UNDO tablespace, if we just want the transaction data size, then we can use the below query

select 'Total Allocated Size : ' ,sum(bytes/1024/1024/1024) , ' Gigabytes' from sys.dba_data_files where tablespace_name not in
('UNDOTBS01','UNDOTBS02','SYSTEM','SYSAUX');

Now Database also includes Temp files, Redo logs files and Control files, So DB size including these

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;

How to check db size in oracle for CDB Database

Now a CDB Database contains many PDBs. So we need to calculate space for each of these pdbs and Container database

How to find the db size of Root Container

ALTER SESSION SET CONTAINER = CDB$ROOT;
select 'Total Allocated Size : ' ,sum(bytes/1024/1024/1024) , ' Gigabytes' from sys.dba_data_files ;

How to find the db size of the PDB

select total_size/1024/1024/1024 "PDB_SIZE_GB" from v$pdbs where name='&1';

The above also includes the temp file also. You can use the below query also if you are in the PDB

ALTER SESSION SET CONTAINER = &pdbname; 
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 ) from dual ;

How to find the size of the whole CDB Database

You can use the below query

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

If you want to include temp files, redo and Control file also, you can use the below query

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

I hope this post on how to check db size in oracle helps in your daily activities

oracle create tablespace: This article on how to create tablespace in oracle, various characteristics associated with it and different create tablespace statements
ORA-01652: ORA-01652 error usually because when the tablespace does not have free space in Permanent and Temporary tablespace in oracle database. Check out how to resolve it
shrink datafile in Oracle: Check out how to shrink the datafile and reclaim space on the filesystem. How to resolve ORA-03297
Oracle database administration tutorial: This lists all the Oracle DBA-related stuff. Very helpful for administration
how to change default tablespace in oracle: Default tablespace is the tablespace where the objects are created when no tablespace name is specified by users. Find out how to check default tablespace
How to check temp tablespace in Oracle: This article is about temp tablespace in Oracle, resize the tempfile, drop the temp file, find the temp usage by Session
alter tablespace add datafile: Check out this post on How to add a datafile to tablespace in Oracle, add tempfile to the temporary tablespace, how to add datafile in ASM
https://docs.oracle.com/cd/B28359_01/server.111/b28310/tspaces002.htm