Sometimes you just need a quick answer to a deceptively simple question:
How big is this Oracle database?
That question can actually mean several different things:
I originally wrote a small script for this purpose years ago. The following is an updated version that provides a much more complete picture while remaining simple enough to run from the command line.
db_overall_size.ksh#!/bin/ksh
###############################################################################
# Script Name..: db_overall_size.ksh
# Description..: Displays Oracle database storage usage by component
# Author.......:
# Original Date: 04/10/2013
# Version......: 2.0
# Modified.....: Modernized database sizing / capacity version
#
# Reports:
# - Database identification
# - SYSTEM / SYSAUX / UNDO / USERS-TOOLS / Application Data
# - Allocated, used and free datafile space
# - TEMP allocation
# - Online redo physical footprint
# - Control file physical footprint
# - Current database physical footprint
# - Datafile autoextend growth ceiling
# - FRA total, used, free and reclaimable space
# - FRA usage by file type
#
# Notes:
# "Used" datafile space is calculated as allocated space minus free extents.
#
# The physical database footprint shown here does NOT include the FRA.
# The FRA is reported separately because it can contain backups,
# archived logs, flashback logs and other recovery-related files.
###############################################################################
RUN_TS=$(date '+%Y%m%d_%H%M%S')
OUTFILE="db_size_${ORACLE_SID}_${RUN_TS}.txt"
echo "Creating database size report: ${OUTFILE}"
sqlplus -s "/ as sysdba" <<EOF
set linesize 220
set pagesize 200
set trimspool on
set tab off
set feedback off
set verify off
spool ${OUTFILE}
prompt
prompt ================================================================================
prompt DATABASE IDENTIFICATION
prompt ================================================================================
column db_name format a14
column db_unique_name format a25
column database_role format a20
column open_mode format a20
column instance_name format a16
column host_name format a35
column version format a15
select d.name db_name,
d.db_unique_name,
d.database_role,
d.open_mode,
i.instance_name,
i.host_name,
i.version
from v\$database d,
v\$instance i;
prompt
prompt ================================================================================
prompt DATAFILE SPACE BY DATABASE COMPONENT
prompt ================================================================================
column component format a25 heading "Database Component"
column alloc_gb format 999,999,990.99 heading "Allocated|GB"
column used_gb format 999,999,990.99 heading "Used|GB"
column free_gb format 999,999,990.99 heading "Free|GB"
column pct_used format 990.99 heading "Used|%"
with
df as
(
select tablespace_name,
sum(bytes) / power(1024,3) alloc_gb
from dba_data_files
group by tablespace_name
),
fs as
(
select tablespace_name,
sum(bytes) / power(1024,3) free_gb
from dba_free_space
group by tablespace_name
),
ts as
(
select d.tablespace_name,
d.alloc_gb,
d.alloc_gb - nvl(f.free_gb,0) used_gb,
nvl(f.free_gb,0) free_gb
from df d
left join fs f
on f.tablespace_name = d.tablespace_name
),
components as
(
select case
when tablespace_name = 'SYSTEM'
then 'SYSTEM'
when tablespace_name = 'SYSAUX'
then 'SYSAUX'
when tablespace_name like 'UNDO%'
then 'UNDO'
when tablespace_name in ('USERS','TOOLS')
then 'USERS / TOOLS'
else 'APPLICATION DATA'
end component,
sum(alloc_gb) alloc_gb,
sum(used_gb) used_gb,
sum(free_gb) free_gb
from ts
group by
case
when tablespace_name = 'SYSTEM'
then 'SYSTEM'
when tablespace_name = 'SYSAUX'
then 'SYSAUX'
when tablespace_name like 'UNDO%'
then 'UNDO'
when tablespace_name in ('USERS','TOOLS')
then 'USERS / TOOLS'
else 'APPLICATION DATA'
end
)
select component,
alloc_gb,
used_gb,
free_gb,
case
when alloc_gb > 0
then used_gb / alloc_gb * 100
end pct_used
from components
order by
case component
when 'SYSTEM' then 1
when 'SYSAUX' then 2
when 'UNDO' then 3
when 'USERS / TOOLS' then 4
when 'APPLICATION DATA' then 5
else 6
end;
prompt
prompt ================================================================================
prompt OVERALL DATAFILE UTILIZATION
prompt ================================================================================
column current_gb format 999,999,990.99 heading "Current|Allocated GB"
column used_gb format 999,999,990.99 heading "Used|GB"
column free_gb format 999,999,990.99 heading "Free|GB"
column max_gb format 999,999,990.99 heading "Autoextend|Maximum GB"
column growth_gb format 999,999,990.99 heading "Potential|Growth GB"
column pct_used format 990.99 heading "Used|%"
with
df as
(
select sum(bytes) current_bytes,
sum(
case
when autoextensible = 'YES'
then greatest(bytes,maxbytes)
else bytes
end
) max_bytes
from dba_data_files
),
fs as
(
select nvl(sum(bytes),0) free_bytes
from dba_free_space
)
select df.current_bytes / power(1024,3) current_gb,
(df.current_bytes-fs.free_bytes) / power(1024,3) used_gb,
fs.free_bytes / power(1024,3) free_gb,
df.max_bytes / power(1024,3) max_gb,
(df.max_bytes-df.current_bytes) / power(1024,3) growth_gb,
case
when df.current_bytes > 0
then (df.current_bytes-fs.free_bytes) /
df.current_bytes * 100
end pct_used
from df, fs;
prompt
prompt ================================================================================
prompt PHYSICAL DATABASE FOOTPRINT
prompt ================================================================================
column component format a30
column size_gb format 999,999,990.99 heading "Size|GB"
with
datafiles as
(
select nvl(sum(bytes),0) bytes
from dba_data_files
),
tempfiles as
(
select nvl(sum(bytes),0) bytes
from dba_temp_files
),
redo as
(
/*
* MEMBERS is included so multiplexed redo members are represented
* in the physical footprint.
*/
select nvl(sum(bytes * members),0) bytes
from v\$log
),
controlfiles as
(
/*
* V\$CONTROLFILE contains each control file copy.
*/
select nvl(sum(block_size * file_size_blks),0) bytes
from v\$controlfile
)
select 'Datafiles' component,
datafiles.bytes / power(1024,3) size_gb
from datafiles
union all
select 'Tempfiles',
tempfiles.bytes / power(1024,3)
from tempfiles
union all
select 'Online Redo - Physical',
redo.bytes / power(1024,3)
from redo
union all
select 'Control Files - Physical',
controlfiles.bytes / power(1024,3)
from controlfiles
union all
select 'TOTAL DB FOOTPRINT',
(datafiles.bytes +
tempfiles.bytes +
redo.bytes +
controlfiles.bytes) / power(1024,3)
from datafiles,
tempfiles,
redo,
controlfiles;
prompt
prompt ================================================================================
prompt TEMPORARY TABLESPACE
prompt ================================================================================
column temp_gb format 999,999,990.99 heading "TEMP|Total GB"
column temp_used_gb format 999,999,990.99 heading "TEMP|Used GB"
column temp_free_gb format 999,999,990.99 heading "TEMP|Free GB"
column pct_used format 990.99 heading "Used|%"
select sum(tablespace_size) / power(1024,3) temp_gb,
sum(tablespace_size-free_space) / power(1024,3) temp_used_gb,
sum(free_space) / power(1024,3) temp_free_gb,
case
when sum(tablespace_size) > 0
then sum(tablespace_size-free_space) /
sum(tablespace_size) * 100
end pct_used
from dba_temp_free_space;
prompt
prompt ================================================================================
prompt FAST RECOVERY AREA
prompt ================================================================================
column fra_name format a60 heading "FRA Location"
column fra_total_gb format 999,999,990.99 heading "FRA|Limit GB"
column fra_used_gb format 999,999,990.99 heading "Used|GB"
column fra_free_gb format 999,999,990.99 heading "Available|GB"
column reclaimable_gb format 999,999,990.99 heading "Reclaimable|GB"
column pct_used format 990.99 heading "Used|%"
select name fra_name,
space_limit / power(1024,3) fra_total_gb,
space_used / power(1024,3) fra_used_gb,
greatest(space_limit-space_used,0) /
power(1024,3) fra_free_gb,
space_reclaimable / power(1024,3) reclaimable_gb,
case
when space_limit > 0
then space_used / space_limit * 100
end pct_used
from v\$recovery_file_dest;
prompt
prompt ================================================================================
prompt FRA CONTENT BY FILE TYPE
prompt ================================================================================
column file_type format a28
column pct_used format 990.99 heading "FRA|Used %"
column pct_reclaim format 990.99 heading "FRA|Reclaimable %"
column number_of_files format 999,999 heading "Files"
select file_type,
percent_space_used pct_used,
percent_space_reclaimable pct_reclaim,
number_of_files
from v\$recovery_area_usage
where percent_space_used > 0
or percent_space_reclaimable > 0
or number_of_files > 0
order by percent_space_used desc;
prompt
prompt ================================================================================
prompt REPORT COMPLETE
prompt ================================================================================
select 'Generated: ' ||
to_char(sysdate,'MM/DD/YYYY HH24:MI:SS')
from dual;
spool off
exit
EOF
echo
echo "Database size report complete."
echo "Report: ${OUTFILE}"
The original script answered the basic sizing question, but this version separates several concepts that are often mistakenly treated as the same number.
Allocated datafile space tells us how large the current Oracle datafiles are.
Used datafile space estimates how much of that allocation is occupied by Oracle segments by subtracting available free extents.
Maximum autoextend size is especially important for capacity planning. A 4 TB database may currently occupy 4 TB while its datafiles are configured to grow to 9 TB. The second number is often more important when determining whether the underlying ASM disk group has sufficient capacity.
Physical database footprint includes datafiles, tempfiles, redo members, and control-file copies. The Fast Recovery Area is intentionally kept separate because adding the FRA to the database size can double-count storage associated with archived logs, backups, flashback logs, and recovery-related files.
The script also shows FRA reclaimable space. This can be extremely useful when investigating storage pressure because "FRA used" does not necessarily mean all of that space must remain permanently occupied.
With one report a DBA can quickly determine:
That makes the script useful not only for troubleshooting, but also for capacity planning, database migrations, Exadata sizing, cloud migrations, storage forecasting, and environment comparisons.
A single database-size report is useful.
A history of those reports is considerably more valuable.
If the results are collected periodically into a central repository, it becomes possible to calculate:
At that point this little DBA utility becomes the foundation of a simple database capacity-management system rather than merely a one-time sizing script.