Database Size and Free Space Analysis (ddbspace.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 comprehensive tablespace space analysis by:

• Displaying file-level space usage for both regular and temporary tablespaces
• Calculating free space percentages and fragmentation metrics
• Showing the largest free extent available in each file
• Combining data files and temporary files in a unified view
• Computing totals and summaries by tablespace

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

SQL> @ddbspace.sql
Enter value for tablespace: %

Parameters

• tablespace: Tablespace name pattern (% for all tablespaces, specific name for single tablespace)

Required Privileges

• SELECT on SYS.DBA_FREE_SPACE
• SELECT on SYS.DBA_DATA_FILES
• SELECT on SYS.DBA_LMT_FREE_SPACE
• SELECT on SYS.DBA_TEMP_FILES
• SELECT on V$PARAMETER

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      45   156,672  15   19,584     8,192     1,024
                   2   524,288      23    78,336  15    9,792     4,096       512
                 ---- --------- ------- --------- --- ------- ----------- ---------
sum                  1,572,864      68   235,008  15   29,376    12,288     1,536

SYSAUX             3   786,432      67   117,504  15   14,688     6,144       768
                 ---- --------- ------- --------- --- ------- ----------- ---------
sum                    786,432      67   117,504  15   14,688     6,144       768

USERS              4   104,857,600   12  52,428,800  50 6,553,600  20,971,520  2,621,440
                   5   104,857,600    8  31,457,280  30 3,932,160  15,728,640  1,966,080
                 ---- --------- ------- --------- --- ------- ----------- ---------
sum                  209,715,200   20  83,886,080  40 10,485,760  36,700,160  4,587,520

TEMP               1   2,147,483,648  1  1,073,741,824  50 134,217,728  1,073,741,824  134,217,728
                 ---- --------- ------- --------- --- ------- ----------- ---------
sum                  2,147,483,648  1  1,073,741,824  50 134,217,728  1,073,741,824  134,217,728

                                     count      4
                 ---- --------- ------- --------- --- ------- ----------- ---------
total                4,716,258,144  156  1,510,243,416  32 188,780,552  1,129,289,472  141,160,624

Key Output Columns

File Information

• TABLESPACE: Tablespace name
• ID: File identifier
• FILE SIZE (K): Total file size in kilobytes

Free Space Metrics

• FREE EXTENTS: Number of free extents in the file
• FREE SIZE (K): Total free space in kilobytes
• FREE PCT: Percentage of file that is free
• FREE BLOCKS: Total free blocks

Fragmentation Analysis

• MAX FREE KBYTES: Size of largest free extent in KB
• MAX FREE BLOCKS: Size of largest free extent in blocks

Understanding the Output

Space Utilization

• High FREE PCT: Plenty of available space
• Low FREE PCT: Approaching capacity limits
• Zero FREE PCT: File is completely full

Fragmentation Assessment

• Many Small Extents: High fragmentation
• Few Large Extents: Low fragmentation
• MAX FREE size: Largest contiguous space available

Analysis Techniques

Capacity Planning

-- Calculate tablespace utilization summary
SELECT tablespace_name,
       ROUND(SUM(bytes)/1024/1024/1024, 2) total_gb,
       ROUND(SUM(free_space)/1024/1024/1024, 2) free_gb,
       ROUND((1 - SUM(free_space)/SUM(bytes)) * 100, 2) used_pct
FROM (
    SELECT tablespace_name, bytes,
           NVL(free_space, 0) free_space
    FROM (
        SELECT tablespace_name, SUM(bytes) bytes
        FROM dba_data_files
        GROUP BY tablespace_name
    ) df,
    (
        SELECT tablespace_name, SUM(bytes) free_space
        FROM dba_free_space
        GROUP BY tablespace_name
    ) fs
    WHERE df.tablespace_name = fs.tablespace_name(+)
)
GROUP BY tablespace_name
ORDER BY used_pct DESC;

Fragmentation Analysis

• High FREE EXTENTS with Small MAX FREE: Fragmented
• Low FREE EXTENTS with Large MAX FREE: Well consolidated
• Many files with small free spaces: Consider coalescing

Common Use Cases

1. Space Monitoring

   • Daily space utilization checks
   • Identify tablespaces approaching capacity
   • Plan for storage expansion

2. Performance Analysis

   • Identify fragmented tablespaces
   • Plan for space reorganization
   • Optimize extent sizing

3. Capacity Planning

   • Project growth requirements
   • Plan file additions
   • Size new tablespaces appropriately

Critical Thresholds

Space Utilization Alerts

• Warning: > 80% utilized
• Critical: > 90% utilized
• Emergency: > 95% utilized

Fragmentation Indicators

• High fragmentation: > 100 free extents per file
• Poor consolidation: MAX FREE < 10% of total free space

Space Management Actions

Adding Space

-- Add new datafile to tablespace
ALTER TABLESPACE users ADD DATAFILE
'/path/to/new/datafile.dbf' SIZE 1G
AUTOEXTEND ON NEXT 256M MAXSIZE 10G;

-- Resize existing datafile
ALTER DATABASE DATAFILE '/path/to/datafile.dbf' RESIZE 2G;

Defragmentation

-- Coalesce free space
ALTER TABLESPACE users COALESCE;

-- Move table to defragment
ALTER TABLE large_table MOVE;
-- Rebuild associated indexes
ALTER INDEX table_idx REBUILD;

Monitoring Automation

Create Monitoring View

CREATE OR REPLACE VIEW tablespace_utilization AS
SELECT tablespace_name,
       ROUND(total_mb, 2) total_mb,
       ROUND(used_mb, 2) used_mb,
       ROUND(free_mb, 2) free_mb,
       ROUND(used_pct, 2) used_pct,
       CASE
         WHEN used_pct > 95 THEN 'CRITICAL'
         WHEN used_pct > 90 THEN 'WARNING'
         WHEN used_pct > 80 THEN 'CAUTION'
         ELSE 'OK'
       END status
FROM (
    SELECT tablespace_name,
           total_space/1024/1024 total_mb,
           (total_space - free_space)/1024/1024 used_mb,
           free_space/1024/1024 free_mb,
           ((total_space - free_space) / total_space) * 100 used_pct
    FROM (
        SELECT tablespace_name,
               SUM(bytes) total_space,
               NVL(SUM(free_bytes), 0) free_space
        FROM (
            SELECT tablespace_name, SUM(bytes) bytes, 0 free_bytes
            FROM dba_data_files
            GROUP BY tablespace_name
            UNION ALL
            SELECT tablespace_name, 0 bytes, SUM(bytes) free_bytes
            FROM dba_free_space
            GROUP BY tablespace_name
        )
        GROUP BY tablespace_name
    )
);

Alert Query

-- Generate alerts for space issues
SELECT tablespace_name, used_pct, status,
       'ALTER TABLESPACE ' || tablespace_name ||
       ' ADD DATAFILE SIZE 1G;' AS suggested_action
FROM tablespace_utilization
WHERE status IN ('WARNING', 'CRITICAL')
ORDER BY used_pct DESC;

Temporary Tablespace Analysis

Temp Space Usage

-- Analyze temporary tablespace usage
SELECT s.tablespace, s.username, s.sid, s.serial#,
       ROUND(s.blocks * p.value / 1024 / 1024, 2) temp_mb
FROM v$sort_usage s, v$parameter p, v$session sess
WHERE p.name = 'db_block_size'
AND s.session_addr = sess.saddr
ORDER BY temp_mb DESC;

Best Practices

1. Regular Monitoring

   • Check space daily during business hours
   • Set up automated alerts
   • Track growth trends

2. Proactive Management

   • Add space before reaching 90% utilization
   • Plan for seasonal usage spikes
   • Monitor fragmentation levels

3. Performance Optimization

   • Keep fragmentation low
   • Size extents appropriately
   • Use locally managed tablespaces

Troubleshooting

ORA-01654: Unable to Extend

• Immediate: Add space or resize files
• Investigation: Check autoextend settings
• Prevention: Implement monitoring alerts

Poor Performance

• Fragmentation: High extent counts
• Solution: Reorganize objects, coalesce free space
• Prevention: Proper extent sizing

Related Scripts

• Tablespace Information
• File Space with Autoextend
• Space Summary by Tablespace