Oracle Datafile Shrink Query – Reclaim Unused Space Fast

Oracle Datafile Shrink Query is an essential technique for DBAs aiming to reclaim unused space in Oracle databases. By using the Oracle Datafile Shrink Query, administrators can identify and resize datafiles efficiently, ensuring optimized disk usage and improved database performance. This guide explains the complete process — from checking block sizes to executing resize commands — with optimized SQL scripts tailored for production environments.

---

Understanding Why Datafiles Grow

Oracle datafiles expand automatically as data is added to tables and indexes. However, when data is deleted or tables are truncated, the associated space isn’t automatically released to the operating system — it remains allocated to the database.

This can result in:

• Wasted disk space that could be used elsewhere.
• Higher backup sizes, since backups include unused portions of the datafiles.
• Performance overhead during full scans or maintenance operations.

The good news? Oracle provides ways to reclaim this space safely by resizing the datafiles to their smallest possible size — without affecting data availability.

---

Steps to Shrink Datafiles and Reclaim Unused Space

The process involves three main steps:

1. Identify your database block size.
2. Check how much space can be reclaimed from each datafile.
3. Execute the resize commands to shrink datafiles safely.

Let’s go through each step in detail.

---

Step 1: Check the Database Block Size

Oracle stores data in blocks, and the block size determines how much data fits into one block. Knowing this helps calculate file sizes accurately.

Run the following query to check your current database block size:

COLUMN value NEW_VAL blksize;
SELECT value FROM v$parameter WHERE name = 'db_block_size';

Output

VALUE
--------------------------
8192

his query retrieves the value of the db_block_size parameter, typically 8192 bytes (8KB) in most Oracle databases.
The value returned will be stored in a substitution variable (&&blksize) for use in later calculations.

---

Step 2: Check How Much Space Can Be Reclaimed

Now that you know the block size, you can calculate how much space each datafile can potentially free.
The query below identifies the high-water mark (HWM) of each datafile — the point up to which data has ever been written. Everything beyond that can be safely reclaimed.

col file_name format a50 word_wrapped
col smallest format 999,990 heading "Smallest|Size|Poss MB."
col currsize format 999,990 heading "Current|Size MB"
col savings  format 999,990 heading "Poss.|Savings MB"

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(+);

output

                                                   Smallest
                                                       Size  Current      Poss.
FILE_NAME                                          Poss MB.  Size MB Savings MB
-------------------------------------------------- -------- -------- ----------
/u01/app/oracle/oradata/DB/datafile/o1_mf_syst      891      900          9
em_nggrrqwt_.dbf

/u01/app/oracle/oradata/DB/datafile/o1_mf_user        3        5          2
s_nggrvw83_.dbf

/u01/app/oracle/oradata/DB/datafile/o1_mf_undo       36       65         29
tbs1_nggrvlfx_.dbf

/u01/app/oracle/oradata/DB/datafile/o1_mf_sysa      505      530         25
ux_nggrtg85_.dbf

What This Query Does

• dba_data_files provides current file details and sizes.
• The subquery calculates the maximum used block ID for each file (the HWM).
• The difference between the current size and the HWM size gives the potential savings in MB.

You can also filter by specific tablespace names if needed:

AND tablespace_name = 'USERS';

🧾 Example Output

FILE_NAME	SMALLEST (MB)	CURRENT (MB)	POSSIBLE SAVINGS (MB)
/u01/oradata/DB1/users01.dbf	512	1024	512
/u01/oradata/DB1/system01.dbf	2048	2048	0

This output shows how much space each file can safely release back to the OS.

---

Step 3: Shrink the Datafiles

Once you’ve identified the savings, it’s time to reclaim that unused space.
The script below generates and executes the ALTER DATABASE DATAFILE RESIZE statements dynamically.

SET PAGES 0
SET LINES 300
COLUMN cmd FORMAT a300 WORD_WRAPPED

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;

output

ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/UPULDB/datafile/o1_mf_system_nggrrqwt_.dbf' RESIZE 891M;
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/UPULDB/datafile/o1_mf_users_nggrvw83_.dbf' RESIZE 3M;
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/UPULDB/datafile/o1_mf_undotbs1_nggrvlfx_.dbf' RESIZE 36M;
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/UPULDB/datafile/o1_mf_sysaux_nggrtg85_.dbf' RESIZE 505M;

How It Works

This query generates resize statements only for files that have reclaimable space.
Each resulting line will look like this:

ALTER DATABASE DATAFILE '/u01/oradata/DB1/users01.dbf' RESIZE 512M;

You can copy these commands and run them manually, or redirect the output to a script file for batch execution.

---

Pro Tips for Shrinking Datafiles Safely

1. Always take a backup before resizing datafiles.
   If something goes wrong, you can restore quickly.
2. Perform the operation during off-peak hours.
   Although it’s generally safe, resizing involves I/O and can cause minor performance impact.
3. Check for free space fragmentation.
   Sometimes, even if datafile size looks reducible, fragmented free space can prevent resizing.
4. Avoid shrinking system or undo tablespaces.
   These are critical for Oracle internals and usually don’t benefit from resizing.
5. Use AWR or OEM to monitor performance after resizing to confirm no unexpected effects.

---

Why You Should Reclaim Unused Space Regularly

Managing space in large databases can be tricky. Over time, Oracle datafiles can grow significantly due to inserts, updates, and deletes, leaving unused storage behind. Using the Oracle Datafile Shrink Query, you can quickly reclaim this unused space and maintain efficient tablespace usage without compromising data integrity.

• Lower storage costs: Freeing up gigabytes or even terabytes of wasted disk space can reduce your infrastructure footprint.
• Faster backups: Smaller datafiles mean quicker backup and restore operations.
• Improved performance: Oracle operations involving file scans, space checks, and I/O become more efficient.
• Healthier database environment: Keeps your database lean, optimized, and easier to maintain.

For large enterprise systems, automating this check as part of your monthly maintenance or capacity planning routine is a great practice.

---

Common Mistakes to Avoid

• Shrinking without validation:
  Don’t blindly resize every datafile. Always check actual HWM to ensure no data loss risk.
• Forgetting TEMP and UNDO tablespaces:
  These can grow dynamically during heavy operations but often reset automatically — they typically shouldn’t be resized manually.
• Ignoring Autoextend settings:
  After shrinking, review your AUTOEXTEND configuration to ensure datafiles can grow when required.

You can verify it with:

SELECT file_name, autoextensible FROM dba_data_files;

Conclusion

Reclaiming unused space from Oracle datafiles is a simple yet powerful optimization that every DBA should know.
By identifying your database block size, calculating reclaimable space, and safely resizing datafiles, you can maintain a healthy and efficient Oracle environment.

The queries shared above are production-safe, easy to automate, and provide immediate visibility into your database’s storage efficiency.

With proactive management and a consistent cleanup routine, you’ll not only save disk space but also keep your Oracle database running faster, cleaner, and more cost-efficient.

---

Related Reading

• Oracle 19c Export/Import Guide: Users, Roles & Tablespaces
• Oracle Documentation: Managing Tablespaces and Datafiles