Database Space Summary by Tablespace (ddbspaceb.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 a tablespace-level summary of database space utilization including autoextend capabilities. Unlike file-level analysis, it aggregates all datafiles within each tablespace to show total current usage, maximum capacity, and available space considering autoextend settings.

Script

rem  ddbspacea.sql
rem
set linesize 80
rem
ttitle 'Database Space Summary by Tablespace|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 skip 1
compute sum of file_bytes max_bytes free_bytes used_bytes file_size on report
rem
select tablespace_name,
       sum(file_bytes)/1024 file_bytes,
       sum(max_bytes)/1024 max_bytes,
       sum(used_bytes)/1024 used_bytes,
       sum(free_bytes)/1024 free_bytes,
       (sum(free_bytes) / sum(max_bytes)) * 100 free_pct
from ( select tablespace_name,
       file_id,
       file_bytes file_bytes,
       max_bytes max_bytes,
       (file_bytes - nvl(free_bytes,0)) used_bytes,
       (max_bytes - (file_bytes - nvl(free_bytes,0))) free_bytes
  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
        )
)
group by tablespace_name
/

Usage

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

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

-- Examples
Enter value for tablespace: %      -- All tablespaces
Enter value for tablespace: USERS  -- Specific tablespace

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 Tablespace
using Autoextend Formulas

TABLESPACE      CURRENT     MAXIMUM   USED SIZE   FREE SIZE  FREE
              FILE SIZE   FILE SIZE         (K)         (K)   PCT
--------------- --------- ----------- ----------- ----------- ------
SYSTEM            1048576    32767968      987654    31780314  97.02
SYSAUX             524288    32767968      456789    32311179  98.60
USERS             7340032    65535936     4691356    60844580  92.84
TEMP              1048576    10485760       12345    10473415  99.88
UNDOTBS1          2097152    32767968     1234567    31533401  96.23
                --------- ----------- ----------- -----------
sum              12058624   174325600    7382711   166942889

Key Output Columns

• TABLESPACE: Tablespace name
• CURRENT FILE SIZE (K): Total current allocated space in KB
• MAXIMUM FILE SIZE (K): Total maximum space with autoextend in KB
• USED SIZE (K): Total space currently used in KB
• FREE SIZE (K): Total available space based on max size in KB
• FREE PCT: Percentage of maximum size that is available

Understanding Tablespace Space

Space Calculation Method

• Current Size: Sum of all datafile current sizes
• Maximum Size: Sum of all datafile maximum sizes (with autoextend)
• Used Space: Current size minus free extents
• Free Space: Maximum size minus used space
• Free Percentage: (Free Space / Maximum Size) × 100

Capacity Planning Benefits

• Tablespace-level view: Overall capacity by logical storage unit
• True capacity: Shows actual available space including growth potential
• Aggregate analysis: Combined view across all datafiles per tablespace
• Autoextend awareness: Accounts for automatic file growth

Performance Analysis

Tablespace Utilization Patterns

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

Critical Thresholds

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

Common Use Cases

Daily Space Monitoring

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

Capacity Planning

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

Database Health Checks

-- Include in daily health monitoring
@ddbspaceb.sql
-- Quick overview of space utilization
-- Identify tablespaces needing attention

Troubleshooting Space Issues

Low Free Space Actions

1. Add new datafile:

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

2. Increase existing maxsize:

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

3. Enable autoextend on non-autoextend files:

   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 by comparing runs
-- Growth Rate = (Today's Used - Yesterday's Used) / Days
SELECT tablespace_name,
       used_size_mb,
       LAG(used_size_mb) OVER (PARTITION BY tablespace_name ORDER BY check_date) prev_used,
       check_date - LAG(check_date) OVER (PARTITION BY tablespace_name ORDER BY check_date) days_diff
FROM tablespace_usage_history
ORDER BY tablespace_name, check_date;

Autoextend Configuration Analysis

-- Check autoextend settings for each tablespace
SELECT tablespace_name,
       COUNT(*) total_files,
       SUM(CASE WHEN autoextensible = 'YES' THEN 1 ELSE 0 END) autoextend_files,
       SUM(CASE WHEN maxbytes = bytes THEN 1 ELSE 0 END) no_growth_files
FROM dba_data_files
GROUP BY tablespace_name
ORDER BY tablespace_name;

Compare with Standard Views

-- Cross-reference with DBA_TABLESPACE_USAGE_METRICS
SELECT tsum.tablespace_name,
       ROUND(tsum.used_space * ts.block_size / 1024 / 1024, 0) used_mb_std,
       ROUND(custom.used_bytes / 1024, 0) used_mb_custom,
       ROUND(tsum.used_percent, 2) used_pct_std
FROM dba_tablespace_usage_metrics tsum,
     dba_tablespaces ts,
     (/* Your custom query results */) custom
WHERE tsum.tablespace_name = ts.tablespace_name
AND tsum.tablespace_name = custom.tablespace_name;

Related Scripts

• Database Space by File (ddbspacea.sql) - File-level space analysis
• Database Files (dfile.sql) - Detailed file information
• Tablespace Definitions (../database-info/dtspace.md) - Tablespace configuration
• Maximum Shrink Analysis (maxshrink.sql) - Space reclamation analysis

Automation and Monitoring

Automated Monitoring Script

-- Create monitoring procedure
CREATE OR REPLACE PROCEDURE monitor_tablespace_space AS
BEGIN
  FOR rec IN (
    SELECT tablespace_name, free_pct
    FROM (/* script query */)
    WHERE free_pct < 20
  ) LOOP
    DBMS_OUTPUT.PUT_LINE('WARNING: '||rec.tablespace_name||
                        ' only has '||rec.free_pct||'% free space');
  END LOOP;
END;
/

Historical Tracking

-- Create history table
CREATE TABLE tablespace_usage_history (
  check_date DATE,
  tablespace_name VARCHAR2(30),
  current_size_mb NUMBER,
  max_size_mb NUMBER,
  used_size_mb NUMBER,
  free_size_mb NUMBER,
  free_pct NUMBER
);

-- Insert daily snapshots
INSERT INTO tablespace_usage_history
SELECT SYSDATE, tablespace_name, file_bytes/1024, max_bytes/1024,
       used_bytes/1024, free_bytes/1024, free_pct
FROM (/* script query */);