How to Find Oracle Database Size

This post provides SQL queries to find the size of an Oracle database, including the total database size, data size, used and free space, and size by owner. It also includes methods to check the overall database size, the space occupied by data segments, and more.

1. The Actual Size of the Database in GB

To find the total size of the database:

SELECT SUM(bytes) / 1024 / 1024 / 1024 AS GB FROM dba_data_files;

2. Size Occupied by Data in the Database

To check the size occupied by data in the database:

SELECT SUM(bytes) / 1024 / 1024 / 1024 AS GB FROM dba_segments;

3. Overall/Total Database Size

To get the overall size of the database, including data files, temp files, redo logs, and control files:

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;

4. Database Size and Space Usage

Alternatively, use the following query to check the total database size, used space, and free space:

SELECT 
  ROUND(SUM(used.bytes) / 1024 / 1024 / 1024) || ' GB' AS "Database Size",
  ROUND(SUM(used.bytes) / 1024 / 1024 / 1024) - ROUND(free.p / 1024 / 1024 / 1024) || ' GB' AS "Used Space",
  ROUND(free.p / 1024 / 1024 / 1024) || ' GB' AS "Free Space"
FROM 
  (SELECT bytes FROM v$datafile 
   UNION ALL 
   SELECT bytes FROM v$tempfile 
   UNION ALL 
   SELECT bytes FROM v$log) used,
  (SELECT SUM(bytes) AS p FROM dba_free_space) free
GROUP BY free.p;