Maximum Shrink Analysis (maxshrink.sql)

What This Script Does

Analyzes datafile space usage by calculating the High Water Mark (HWM) for each datafile and determines how much space can be reclaimed by shrinking datafiles. The script provides both analysis and ready-to-execute resize commands.

The Script

rem  maxshrink.sql
rem
ttitle 'Maximum Shrink Analysis'
rem
set verify off
rem
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings  format 999,990 heading "Poss.|Savings"
rem
break on report
compute sum of savings on report
rem
column value new_val blksize
rem
select value from v$parameter where name = 'db_block_size'
/
rem
select file_name,
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
       ceil( blocks*&&blksize/1024/1024) currsize,
       ceil( blocks*&&blksize/1024/1024) -
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+)
/
rem
column cmd format a75 word_wrapped
rem
select 'alter database datafile '''||file_name||''' resize ' ||
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 )  || 'm;' cmd
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+)
  and ceil( blocks*&&blksize/1024/1024) -
      ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
/

Usage

-- Analyze all datafiles for shrink opportunities
@maxshrink.sql

Parameters

The script requires no input parameters and analyzes all datafiles automatically.

Required Privileges

SELECT ANY DICTIONARY
-- OR --
SELECT on DBA_DATA_FILES
SELECT on DBA_EXTENTS
SELECT on V$PARAMETER

Sample Output

Part 1: Space Analysis

                                Maximum Shrink Analysis

FILE_NAME                                          Smallest Current   Poss.
                                                   Size     Size    Savings
                                                   Poss.
-------------------------------------------------- -------- ------- -------
/u01/oradata/prod/system01.dbf                         750     800      50
/u01/oradata/prod/sysaux01.dbf                         450     500      50
/u01/oradata/prod/undotbs01.dbf                        200     400     200
/u01/oradata/prod/users01.dbf                          100     300     200
/u01/oradata/prod/data01.dbf                         1,200   2,000     800
/u01/oradata/prod/index01.dbf                          800   1,500     700
                                                                    -------
SUM                                                                   2,000

Part 2: Resize Commands

CMD
---------------------------------------------------------------------------
alter database datafile '/u01/oradata/prod/undotbs01.dbf' resize 200m;
alter database datafile '/u01/oradata/prod/users01.dbf' resize 100m;
alter database datafile '/u01/oradata/prod/data01.dbf' resize 1200m;
alter database datafile '/u01/oradata/prod/index01.dbf' resize 800m;

Key Output Columns

Analysis Section

FILE_NAME: Full path to the datafile
Smallest Size Poss.: Minimum possible size in MB (based on HWM)
Current Size: Current datafile size in MB
Poss. Savings: Potential space savings in MB

Command Section

CMD: Ready-to-execute ALTER DATABASE commands

How It Works

High Water Mark Calculation

-- Finds the highest used block for each datafile
select file_id, max(block_id+blocks-1) hwm
  from dba_extents
 group by file_id

Space Calculation

-- Converts blocks to MB and calculates savings
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest
ceil( blocks*&&blksize/1024/1024) currsize

Common Use Cases

Space Reclamation

-- Identify opportunities to reclaim disk space
@maxshrink.sql
-- Execute generated resize commands

Capacity Planning

-- Understand actual vs. allocated space
@maxshrink.sql
-- Plan for future growth requirements

Storage Cost Optimization

-- Reduce storage costs by reclaiming unused space
@maxshrink.sql
-- Focus on datafiles with largest savings potential

Database Maintenance

-- Regular space analysis during maintenance windows
@maxshrink.sql
-- Implement proactive space management

Important Considerations

Safety Precautions

Always perform during maintenance windows
Ensure adequate free space exists for operations
Test resize commands in non-production first
Have backup available before resizing

Limitations

Cannot shrink below the high water mark
Some space may be needed for extent allocation
Consider adding small buffer to calculated minimum

Performance Impact

Resize operations may impact database performance
Schedule during low-activity periods
Monitor space usage after resizing

Execution Tips

Review Before Execution

-- Examine the generated commands carefully
-- Verify datafile paths and sizes
-- Consider adding safety margins

Modify Commands if Needed

-- Add buffer space to minimum sizes
alter database datafile '/path/file.dbf' resize 205m;  -- Instead of 200m

Execute Commands Individually

-- Execute one at a time to monitor progress
-- Check for errors after each command
-- Verify space reclamation

Monitoring Results

Verify Space Reclamation

-- Check tablespace usage after resize
SELECT tablespace_name,
       round(bytes/1024/1024) current_mb,
       round(maxbytes/1024/1024) max_mb
FROM dba_data_files;

Monitor Database Performance

-- Ensure no performance degradation
-- Watch for space allocation issues
-- Monitor extent allocation patterns

fra_space - FRA space usage analysis
dfile - Database files information
Tablespace usage scripts for comprehensive space management