Reclaiming Oracle Tablespace Space Using HWM Logic: On-Prem and Cloud-Aware Automation

In enterprise-grade Oracle environments—whether fully on-premises, hybrid, or actively transitioning to Oracle Cloud Infrastructure (OCI)—efficient storage management remains a mission-critical responsibility for database administrators (DBAs). One of the persistent challenges DBAs face is space wastage within tablespaces. Oracle allocates extents dynamically as segments grow, but does not automatically shrink datafiles once the underlying segments are dropped, truncated, or reorganized. As a result, unused yet allocated space accumulates over time, unnecessarily inflating datafiles and leading to inefficiencies in storage, backup, and performance.

This article introduces a robust PL/SQL script designed to automate the reclamation of unused space in Oracle tablespaces by calculating the High Water Mark (HWM) and issuing safe, conditional resize commands. This approach not only avoids common pitfalls of naive datafile shrinking but also integrates seamlessly with operational and maintenance routines. The script is applicable across a variety of Oracle deployment models, including:

• Legacy or air-gapped on-premises systems
• Hybrid cloud environments where data is synchronized with cloud storage
• Staging or cleanup operations during cloud migration to DBaaS platforms such as Oracle Autonomous Database

The goal is to empower DBAs with a reusable and safe mechanism to maintain a lean storage footprint and reduce unnecessary resource consumption.

Why Tablespace Bloat Happens

In Oracle databases, segments such as tables, indexes, and LOBs consume extents that are allocated in datafiles. When these segments grow, the high water mark moves forward. However, when segments are dropped or reorganized, the HWM is not automatically reduced, and Oracle does not shrink the datafile by default. Consequently, datafiles continue to occupy more space on disk than is actually needed.

This phenomenon leads to multiple operational inefficiencies:

• RMAN and third-party backups include unused space, leading to longer backup windows and higher storage costs.
• Storage tiering and capacity planning become skewed due to artificially inflated space consumption.
• Cloud backup or replication services, particularly in hybrid environments, may end up transferring or storing unnecessary data.

Periodic monitoring and reclamation based on HWM analysis is essential to prevent such issues and keep storage usage optimized.

The PL/SQL Script: Safe Resizing Based on HWM

The script below analyzes each datafile in the specified tablespaces, determines its actual used space based on the high water mark, and conditionally resizes the file to a safe minimum size. It includes retry logic in case of failed attempts and skips files where resizing is not beneficial. The filtering pattern for tablespaces can be customized to target specific environments.

Tip: If the query in the cursor is taking too long to run, try adding hint /*+ RULE */

SET ECHO OFF
SET FEEDBACK OFF
SET SERVEROUTPUT ON
WHENEVER SQLERROR EXIT FAILURE
WHENEVER OSERROR  EXIT FAILURE

PROMPT Reclaiming unused tablespace space...

DECLARE
    -- Modify this pattern to match your target tablespaces
    l_tablespace_filter  VARCHAR2(30) := '%';
    
    CURSOR cur_datafiles IS
        SELECT a.file_name,
               a.tablespace_name,
               CEIL((NVL(hwm, 1) * c.block_size) / 1024 / 1024) AS hwm_mb,
               CEIL(a.blocks * c.block_size / 1024 / 1024) AS current_mb,
               (CEIL(a.blocks * c.block_size / 1024 / 1024) -
               CEIL((NVL(hwm, 1) * c.block_size) / 1024 / 1024)) AS reclaimable_mb
        FROM dba_data_files a
        JOIN dba_tablespaces c ON a.tablespace_name = c.tablespace_name
        LEFT JOIN (
            SELECT file_id,
                   MAX(block_id + blocks - 1) AS hwm
            FROM dba_extents
            GROUP BY file_id
        ) b ON a.file_id = b.file_id
        WHERE c.contents = 'PERMANENT'
          AND (CEIL(a.blocks * c.block_size / 1024 / 1024) -
               CEIL((NVL(hwm, 1) * c.block_size) / 1024 / 1024)) > 256;

    stmt            VARCHAR2(4000);
    resize_success  BOOLEAN;
    target_size_mb  NUMBER;
    attempt_mb      NUMBER;
BEGIN
    FOR df IN cur_datafiles LOOP
        DBMS_OUTPUT.put_line('Analyzing file: ' || df.file_name);
        resize_success := FALSE;

        target_size_mb := GREATEST(df.hwm_mb, 256);
        attempt_mb := target_size_mb;

        FOR attempt IN 1..10 LOOP
            EXIT WHEN resize_success;
            
            stmt := 'ALTER DATABASE DATAFILE ''' || df.file_name ||
                    ''' RESIZE ' || attempt_mb || 'M';
            BEGIN
                EXECUTE IMMEDIATE stmt;
                DBMS_OUTPUT.put_line('Resized to: ' || attempt_mb || ' MB');
                resize_success := TRUE;
            EXCEPTION
                WHEN OTHERS THEN
                    DBMS_OUTPUT.put_line('Resize failed at: ' || attempt_mb ||
                                         ' MB. Retrying...');
                    attempt_mb := attempt_mb + 100;
            END;
        END LOOP;

        IF NOT resize_success THEN
            DBMS_OUTPUT.put_line('Failed to resize file: ' || df.file_name ||
                                 ' after multiple attempts.');
        END IF;
    END LOOP;
END;
/

Using This Script in Different Environments

On-Premises Environments

This script is well-suited for traditional on-premises Oracle Database environments, especially those running Oracle 11g, 12c, or 19c in non-autonomous configurations. It can be scheduled via cron or integrated into periodic maintenance procedures to help manage storage proactively. Many legacy systems with fixed storage allocations benefit significantly from this type of housekeeping.

Oracle Cloud Infrastructure (OCI)

While Oracle Autonomous Database does not expose datafile-level operations, the underlying logic of HWM-based space usage still applies. For managed or semi-managed environments like OCI DBaaS or Exadata Cloud@Customer, DBAs can run this script in diagnostic mode to identify which tablespaces are bloated. In environments where segment movement is allowed, the output can be used to guide table or LOB reorganization strategies.

Hybrid Deployments

In hybrid architectures where an on-premise Oracle instance is synced to the cloud (e.g., via RMAN backups or OCI Storage Gateway), reclaiming unused space prior to synchronization results in reduced transfer times and lower cloud storage consumption. This script helps enforce data hygiene at the source before replication.

Output Example

A sample run of the script may produce output like the following, showing the savings achieved per file:

This data can also be captured in an internal log table for audit and historical tracking purposes.

Caution and Considerations

While the presented script is designed with safeguards to minimize risk, there are several important caveats and considerations DBAs must keep in mind before deploying it in production environments:

1. Minimum File Size Thresholds:
   The script imposes a lower limit of 256 MB per datafile. This may not be appropriate for all use cases—some systems may require higher thresholds depending on anticipated growth, performance expectations, or underlying storage constraints.
2. Segment Movement Does Not Lower HWM:
   Dropping or moving objects (e.g., LOBs, tables, partitions) does not always result in an immediate reduction of the high water mark. Therefore, reorganizing or shrinking segments prior to running this script is often necessary to achieve meaningful space reclamation.
3. Impact on Backups and Redo Generation:
   Frequent resizing operations may influence RMAN backup strategies or generate redo during segment reorganization. Review backup policies and adjust maintenance windows to avoid overlap.
4. Compatibility with ASM and OMF:
   In environments using ASM (Automatic Storage Management) or OMF (Oracle Managed Files), direct resizing may not be supported or advisable. Always validate compatibility in a test environment before applying the script to such configurations.
5. Error Handling Is Conservative:
   The retry logic increases file size in 100 MB increments upon failure. In some edge cases, this might not be sufficient to complete a resize due to fragmentation or I/O limitations. Monitor script logs and tune retry behavior as needed.
6. Execution Privileges:
   The script requires appropriate privileges (ALTER DATABASE, access to DBA_* views, etc.). Ensure that the execution context has been securely granted the necessary rights and that it adheres to your organization's change control procedures.
7. Dry Run Strongly Recommended:
   Before enabling actual resizing operations, perform a dry run by commenting out the EXECUTE IMMEDIATE statement and replacing it with a DBMS_OUTPUT.PUT_LINE call. This allows safe analysis of proposed changes without affecting datafiles.
8. Autonomous Environments:
   This script is not applicable to Oracle Autonomous Database where storage management is abstracted. Use this only in environments where DBAs control physical storage structures.

By exercising caution and following best practices, this script can become a valuable part of your storage hygiene toolkit.

Conclusion

This HWM-based tablespace reclamation script provides Oracle DBAs with a structured, low-risk approach to reducing unnecessary datafile bloat. It is particularly effective in environments where aggressive space optimization is needed but resizing cannot be safely done arbitrarily. By automating and auditing the resizing process, teams can better align disk usage with actual data volume, improve backup efficiency, and reduce costs in both on-premises and cloud-linked deployments.

For further enhancements, this script can be integrated into Oracle Scheduler, extended with logging to a custom monitoring table, or enhanced with email alerts for failed attempts. The logic can also serve as the foundation for broader space optimization frameworks tailored to specific operational requirements.