OracleStorage

Database size three ways: allocated, used and total

Allocated datafile size, space actually used by segments, and the overall footprint including temp files, online redo logs and control files. All in GB.

Tested on 12c, 19c · verified Sep 2026

1CLEAR COLUMNS2SET LINESIZE 120 PAGESIZE 100 TRIMOUT ON TAB OFF3 4-- Actual size of the database (allocated datafiles)5SELECT SUM (bytes) / 1024 / 1024 / 1024 AS GB FROM dba_data_files;6 7-- Size occupied by data (segments)8SELECT SUM (bytes)/1024/1024/1024 AS GB FROM dba_segments;9 10-- Overall size: datafiles + temp files + redo logs + control files11select12( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +13( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +14( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +15( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB"16from17dual;

Save it as ora-db-size.sql and run it with SQL> @ora-db-size.

Open in denrepoRelated tool: Tablespace runway and datafiles

More Oracle scripts: Storage

• Datafile usage with a bar graphEvery datafile with its size, used space, max size and a ten-character usage bar (X = used, - = free). Free_MB is the largest free extent in the…
• Database size summary: total, used and freeOne line with the database name and its total size (datafiles, temp files and redo logs), used space and free space, rounded to whole GB.
• Size of one schemaTotal segment size in GB for the schema you enter.
• Tablespace usageUsed and free space in GB against the maximum each tablespace can autoextend to, with its type (permanent, temporary or undo), fullest first.
• Datafiles with size and autoextend limitsEvery datafile, its current size, whether it can grow, and how far. Files with AUTOEXTEND NO in a busy tablespace are the ones that page you at night.
• Top 20 largest segmentsThe biggest tables, indexes, LOBs and partitions in the database.