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: