Skip to content
Oracle DBA Scripts & Database Utilities

Database Space Report (ddbspace.sql)

What This Script Does

Section titled “What This Script Does”

This script provides a comprehensive space analysis for database tablespaces, including:

  • File sizes and IDs for each datafile
  • Free space available in each file
  • Fragmentation information (number of free extents)
  • Largest contiguous free space chunk
  • Works for both permanent and temporary tablespaces

The Script

Section titled “The Script”
rem  ddbspace.sql
rem
rem  linesize = 74
set linesize 132
rem
ttitle 'Database Size and Free Space'
rem
col tablespace_name format a15 heading 'TABLESPACE'
col file_id format 999 heading 'ID'
col file_bytes format 999,999,999 heading 'FILE SIZE|(K)'
col free_extents format 99,999 heading 'FREE|EXTENTS'
col free_bytes format 99,999,999 heading 'FREE SIZE|(K)'
col free_pct format 999 heading 'FREE|PCT'
col free_blocks format 9,999,999 heading 'FREE|BLOCKS'
col max_free_blocks format 9,999,999 heading 'MAX FREE|BLOCKS'
col max_free_kbytes format 99,999,999 heading 'MAX FREE|KBYTES'
rem
break on report on tablespace_name skip 1
compute sum of file_bytes free_extents free_bytes free_blocks -
     on report tablespace_name
compute count of file_id on report
rem
define tbspace='&tablespace'
rem
select df.tablespace_name, df.file_id,
       df.bytes/1024 file_bytes,
       count(fs.file_id) free_extents,
       sum(fs.bytes)/1024 free_bytes,
       sum(fs.bytes) * 100 / df.bytes free_pct,
       sum(fs.blocks) free_blocks,
       max(fs.bytes)/1024 max_free_kbytes,
       max(fs.blocks) max_free_blocks
  from sys.dba_free_space fs, sys.dba_data_files df
 where df.file_id = fs.file_id(+)
   and df.tablespace_name = fs.tablespace_name(+)
   and df.tablespace_name like nvl(upper('&tbspace'),'%')
 group by df.tablespace_name, df.file_id, df.bytes
union all
select df.tablespace_name, df.file_id,
       df.bytes/1024 file_bytes,
       count(fs.file_id) free_extents,
       sum(fs.blocks*p.value)/1024 free_bytes,
       sum(fs.blocks*p.value) * 100 / df.bytes free_pct,
       sum(fs.blocks) free_blocks,
       max(fs.blocks*p.value)/1024 max_free_kbytes,
       max(fs.blocks) max_free_blocks
  from sys.DBA_LMT_FREE_SPACE fs,
       v$parameter p,
       sys.dba_temp_files df
 where df.file_id = fs.file_id(+)
   and p.name='db_block_size'
   and df.tablespace_name like nvl(upper('&tbspace'),'%')
 group by df.tablespace_name, df.file_id, df.bytes
 order by tablespace_name, file_id;
rem
undefine tbspace

Usage

Section titled “Usage”
-- Basic usage (all tablespaces)
@ddbspace.sql
-- Enter: Press Enter when prompted for tablespace


-- Specific tablespace
@ddbspace.sql
-- Enter: USERS for tablespace

Parameters

Section titled “Parameters”

The script prompts for:

  • &tablespace - Tablespace name (optional, % or blank for all)

Required Privileges

Section titled “Required Privileges”
SELECT ON DBA_FREE_SPACE
SELECT ON DBA_DATA_FILES
SELECT ON DBA_TEMP_FILES
SELECT ON DBA_LMT_FREE_SPACE
SELECT ON V$PARAMETER

Sample Output

Section titled “Sample Output”
Database Size and Free Space


TABLESPACE       ID   FILE SIZE     FREE    FREE SIZE  FREE    FREE   MAX FREE  MAX FREE
                            (K)  EXTENTS          (K)  PCT   BLOCKS     KBYTES    BLOCKS
--------------- --- ------------ -------- ------------ ---- ---------- ---------- ----------
SYSTEM            1    1,048,576       12      123,456   12     15,432     65,536      8,192
                     ------------          ------------      ----------
sum               1    1,048,576       12      123,456           15,432


SYSAUX            2    2,097,152       25      524,288   25     65,536    262,144     32,768
                     ------------          ------------      ----------
sum               1    2,097,152       25      524,288           65,536


USERS             3    5,242,880        8    1,048,576   20    131,072    524,288     65,536
                  4    5,242,880       15    2,097,152   40    262,144  1,048,576    131,072
                     ------------          ------------      ----------
sum               2   10,485,760       23    3,145,728          393,216


                     ------------          ------------      ----------
sum               4   18,825,488       60    3,793,472          474,184
count             4

Key Output Columns

Section titled “Key Output Columns”
  • TABLESPACE - Tablespace name
  • ID - File ID number
  • FILE SIZE (K) - Total file size in kilobytes
  • FREE EXTENTS - Number of free space chunks (fragmentation indicator)
  • FREE SIZE (K) - Total free space in kilobytes
  • FREE PCT - Percentage of file that is free
  • FREE BLOCKS - Total free blocks
  • MAX FREE KBYTES - Largest contiguous free space chunk
  • MAX FREE BLOCKS - Largest contiguous free blocks

Common Use Cases

Section titled “Common Use Cases”

Capacity Planning

@ddbspace.sql
-- Review FREE PCT column
-- Plan for growth when < 20% free

Fragmentation Analysis

@ddbspace.sql
-- High FREE EXTENTS with low MAX FREE indicates fragmentation
-- Consider tablespace reorganization

Add Datafile Decisions

@ddbspace.sql
-- Enter: USERS for tablespace
-- If MAX FREE KBYTES is too small for new objects, add datafile

Space Management Tips

Section titled “Space Management Tips”

Fragmentation:

  • Many free extents = fragmented tablespace
  • Compare MAX FREE to total FREE SIZE
  • If MAX FREE << FREE SIZE, space is fragmented

Growth Planning:

  • Monitor FREE PCT trends over time
  • Set up alerts for tablespaces < 15% free
  • Consider autoextend settings

Temporary Tablespace:

  • Shows current allocation, not high water mark
  • Space is reused, so high usage is normal
Section titled “Related Scripts”