Database Space with Autoextend (ddbspacea.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This script provides an enhanced database space analysis that accounts for autoextend settings, showing the true available capacity rather than just current allocated space. It calculates free space based on maximum file sizes, giving DBAs accurate capacity planning information for tablespaces with autoextensible datafiles.

Script

rem  ddbspacea.sql
rem
set linesize 80
rem
ttitle 'Database Space Summary by Datafile|using Autoextend Formulas'
rem
col tablespace_name format a15 heading 'TABLESPACE'
col file_id format 999 heading 'ID'
col file_bytes format 999,999,999 heading 'CURRENT|FILE SIZE|(K)'
col max_bytes  format 999,999,999 heading 'MAXIMUM|FILE SIZE|(K)'
col free_bytes format 999,999,999 heading 'FREE SIZE|(K)'
col used_bytes format 999,999,999 heading 'USED SIZE|(K)'
col free_pct format 999.99 heading 'FREE|PCT'
rem
break on report on tablespace_name skip 1
compute sum of file_bytes max_bytes free_bytes used_bytes file_size -
     on report tablespace_name
rem

select tablespace_name,
       file_id,
       file_bytes/1024 file_bytes,
       max_bytes/1024 max_bytes,
       (file_bytes - nvl(free_bytes,0))/1024 used_bytes,
       (max_bytes - (file_bytes - nvl(free_bytes,0)))/1024 free_bytes,
       ((max_bytes - (file_bytes - nvl(free_bytes,0)))/max_bytes) * 100 free_pct
  from (
        select df.tablespace_name, df.file_id,
               df.bytes file_bytes,
               greatest(df.maxbytes,df.bytes) max_bytes,
               sum(fs.bytes) free_bytes
          from sys.dba_free_space fs, (select * from sys.dba_data_files union all select * from sys.dba_temp_files) df
         where df.file_id = fs.file_id(+)
           and df.tablespace_name = fs.tablespace_name(+)
           and df.tablespace_name like nvl(upper('&tablespace'),'%')
         group by df.tablespace_name, df.file_id, df.bytes,df.maxbytes
        )

/

Usage

-- Run the script in SQL*Plus or SQLcl
@ddbspacea.sql

-- When prompted, enter:
-- tablespace: Tablespace name pattern (% for all)

-- Examples
Enter value for tablespace: %
-- Or filter specific tablespace
Enter value for tablespace: USERS

Parameters

• &tablespace: Tablespace name pattern (use % for all tablespaces)

Required Privileges

• SELECT on SYS.DBA_FREE_SPACE
• SELECT on SYS.DBA_DATA_FILES
• SELECT on SYS.DBA_TEMP_FILES
• Typically requires DBA role

Sample Output

Database Space Summary by Datafile
using Autoextend Formulas

TABLESPACE       ID     CURRENT     MAXIMUM   USED SIZE   FREE SIZE  FREE
                      FILE SIZE   FILE SIZE         (K)         (K)   PCT
                            (K)         (K)
--------------- --- ----------- ----------- ----------- ----------- ------
SYSTEM            1   1,048,576  32,767,968     987,654  31,780,314  97.02
                                ----------- ----------- -----------
sum               1   1,048,576  32,767,968     987,654  31,780,314

SYSAUX            2     524,288  32,767,968     456,789  32,311,179  98.60
                                ----------- ----------- -----------
sum               2     524,288  32,767,968     456,789  32,311,179

USERS             3   5,242,880  32,767,968   3,456,789  29,311,179  89.45
                  4   2,097,152  32,767,968   1,234,567  31,533,401  96.23
                                ----------- ----------- -----------
sum               7   7,340,032  65,535,936   4,691,356  60,844,580

TEMP              1   1,048,576  10,485,760      12,345  10,473,415  99.88
                                ----------- ----------- -----------
sum               1   1,048,576  10,485,760      12,345  10,473,415

Key Output Columns

• TABLESPACE: Tablespace name
• ID: Datafile ID number
• CURRENT FILE SIZE (K): Current allocated size in KB
• MAXIMUM FILE SIZE (K): Maximum size with autoextend in KB
• USED SIZE (K): Space currently used in KB
• FREE SIZE (K): Available space based on max size in KB
• FREE PCT: Percentage of maximum size that is available

Understanding Autoextend Space

Space Calculation Method

• Used Space: Current file size minus free extents
• Free Space: Maximum file size minus used space
• Free Percentage: (Free Space / Maximum Size) × 100

Autoextend Considerations

• MAXSIZE set: Shows actual growth potential
• MAXSIZE unlimited: Uses system limits (32GB for smallfile)
• No autoextend: Max size equals current size

Capacity Planning Benefits

• True capacity: Shows actual available space including growth
• Proactive management: Identify tablespaces approaching limits
• Accurate forecasting: Plan based on maximum potential

Performance Analysis

Space Utilization Patterns

1. High FREE PCT (>90%): Plenty of growth capacity
2. Medium FREE PCT (50-90%): Normal utilization
3. Low FREE PCT (<50%): Monitor growth closely
4. Very low FREE PCT (<10%): Immediate attention needed

Critical Thresholds

• FREE PCT < 20%: Consider adding datafiles
• FREE PCT < 10%: Risk of space exhaustion
• FREE PCT < 5%: Critical - immediate action required

Common Use Cases

Daily Space Monitoring

-- Check all tablespace capacity
@ddbspacea.sql
-- Enter % for all tablespaces
-- Focus on low FREE PCT values

Capacity Planning

-- Analyze specific tablespace growth potential
@ddbspacea.sql
-- Enter specific tablespace name
-- Calculate time to exhaustion based on growth rate

Autoextend Validation

-- Verify autoextend settings are appropriate
@ddbspacea.sql
-- Compare current vs maximum sizes
-- Identify files without autoextend

Troubleshooting Space Issues

Low Free Space Actions

1. Increase MAXSIZE:

   ALTER DATABASE DATAFILE '/path/to/file.dbf'
   AUTOEXTEND ON MAXSIZE 50G;

2. Add new datafile:

   ALTER TABLESPACE tablespace_name
   ADD DATAFILE '/path/to/new_file.dbf'
   SIZE 1G AUTOEXTEND ON MAXSIZE 32G;

3. Enable autoextend:

   ALTER DATABASE DATAFILE '/path/to/file.dbf'
   AUTOEXTEND ON NEXT 100M MAXSIZE 32G;

Space Monitoring Best Practices

1. Regular monitoring: Run daily during peak growth periods
2. Threshold alerts: Set up monitoring for FREE PCT < 20%
3. Growth trending: Track usage patterns over time
4. Proactive management: Add space before critical thresholds

Advanced Analysis

Growth Rate Calculation

-- Calculate daily growth rate
-- Run this script daily and compare used_bytes
-- Growth Rate = (Today's Used - Yesterday's Used) / Days

Tablespace-Level Summary

-- Aggregate all datafiles by tablespace
SELECT tablespace_name,
       COUNT(*) datafiles,
       SUM(file_bytes)/1024/1024 current_size_mb,
       SUM(max_bytes)/1024/1024 max_size_mb,
       SUM(used_bytes)/1024/1024 used_mb,
       SUM(free_bytes)/1024/1024 free_mb,
       ROUND(SUM(free_bytes)/SUM(max_bytes)*100, 2) free_pct
FROM (/* query from script */)
GROUP BY tablespace_name
ORDER BY free_pct;

Identify Non-Autoextend Files

-- Files where current size equals max size
SELECT tablespace_name, file_id
FROM dba_data_files
WHERE bytes = maxbytes
OR maxbytes = 0;

Related Scripts

• Database Space Usage (ddbspace.sql) - Basic space analysis without autoextend
• Tablespace Definitions (../database-info/dtspace.md) - Tablespace configuration
• Database Files (dfile.sql) - Detailed file information
• Maximum Shrink Analysis (maxshrink.sql) - Space reclamation analysis