Oracle 23ai Tablespace Shrink

1. Introduction to the Oracle 23ai Tablespace Shrink Feature

The Oracle 23ai database version introduces a significant innovation in storage management, addressing one of the most common and critical challenges faced by Database Administrators (DBAs): the Tablespace Shrink feature. In today’s digital age, data volumes are growing exponentially, leading to increased storage costs and greater complexity in managing storage infrastructures. This feature, introduced with Oracle 23ai, offers the potential to both optimize storage costs and improve database performance by effectively reclaiming unused disk space that accumulates within tablespaces over time. Oracle’s overall strategy is geared towards simplifying and automating database management tasks; the Tablespace Shrink feature aligns with this vision, enabling DBAs to work more efficiently.  

The development of such intelligent storage management features is closely linked to Oracle’s “Autonomous Database” vision and the flexible storage demands in cloud computing. In cloud environments, storage resources are often based on a “pay-as-you-go” model, where every unused byte translates to unnecessary costs. Manual space management can be both time-consuming and prone to errors. Automated or semi-automated space reclamation mechanisms like Tablespace Shrink have the potential to significantly reduce these costs and the management burden. This aligns perfectly with Oracle’s cloud strategy and its goal of evolving databases into self-sufficient, resource-efficient “Autonomous” systems. Furthermore, this feature holds significant value not only for large-scale enterprise systems but also for environments with more limited resources, such as Oracle Database Free. The fact that the SYSAUX tablespace, which can grow over time due to components like AWR data and scheduled task logs, can also be shrunk offers users operating within the 12 GB user data limit of Oracle Database Free the ability to allocate more space for application data. This can be seen as a reflection of Oracle’s strategy to support the developer community and smaller-scale users.  

2. What is Tablespace Shrink and Why is it Important for Database Optimization?

At its core, the Tablespace Shrink operation is the process of reorganizing data segments (e.g., tables, indexes) within an Oracle tablespace to consolidate unused free space accumulated at the end of data files, and then reducing the physical size of these data files to return the freed disk space to the operating system. Databases are dynamic structures; continuous data insertion, deletion, and update operations result in unused spaces, or “fragmentation,” within tablespaces. Tablespace Shrink aims to make this idle space reusable.  

The importance of this feature can be summarized in several key points:

• Space Reclamation and Cost Optimization: Its most apparent benefit is the recovery of unused (“empty”) disk space left behind after objects are deleted or moved elsewhere in the database. This translates directly into cost savings, especially in enterprise environments where storage costs are high or in cloud-based systems where capacity planning is critical. Studies have shown that tablespace shrinking operations reduce total storage costs and postpone the need for additional disk purchases. Oracle 23ai’s feature “allows you to reclaim unused space in the database, reducing costs and optimizing storage”.  
• Performance Improvement: Reducing data fragmentation can lead to noticeable increases in query performance. When data is stored in more organized and contiguous blocks, operations like full table scans or wide-ranging index range scans can be completed with fewer I/O operations. This shortens disk read times and allows for more efficient use of the database buffer cache. One case study reported that a 30% reduction in tablespace size led to a 25% improvement in query performance.  
• Reduced Backup and Restore Times: As the size of data files decreases, the time required to back them up and restore them in a disaster recovery scenario also decreases. This is a critical advantage, especially for large databases with tight backup windows and stringent recovery time objectives (RTO).  
• Simplified Management: A more organized, less fragmented, and optimized tablespace structure simplifies overall database administration.

Before Oracle 23ai, shrinking tablespaces was often a cumbersome and complex process. DBAs frequently encountered the $ORA-03297$: file contains used data beyond requested RESIZE value error when attempting to shrink a data file. This error occurred because there were still in-use data blocks anywhere in the data file beyond the desired shrink boundary. To overcome this, methods such as ALTER TABLE... MOVE, online table redefinition with the DBMS_REDEFINITION package, recreating the table using CREATE TABLE AS SELECT (CTAS), or exporting and re-importing the table were used. However, these older methods were generally complex, time-consuming, required significant additional temporary storage, and often meant downtime for the affected objects. The Tablespace Shrink feature significantly mitigates these challenges by offering a simpler, integrated solution.  

Tablespace Shrink can be viewed not just as a reactive tool (i.e., reclaiming free space after a large data deletion) but also as part of a proactive storage management strategy. Databases are inherently dynamic; continuous data insertion, deletion, and update operations lead to fragmentation and the accumulation of unused space over time. Therefore, by periodically assessing the state of tablespaces using the DBMS_SPACE.SHRINK_TABLESPACE procedure in analyze mode (TS_SHRINK_MODE_ANALYZE) and applying the shrink operation (TS_SHRINK_MODE_SHRINK or other appropriate modes) when necessary—not just after major cleanup operations—storage health and efficiency can be consistently maintained at a high level. This proactive approach can help prevent sudden storage issues, performance degradation, and unnecessary storage costs. Performance improvements are not limited to end-user queries but can also provide indirect benefits in Oracle’s internal background processes, such as accessing data file headers and extent management. Compacting segments and reducing disorganization can mean less physical and logical I/O, not only for full table scans but also for index accesses and even Data Manipulation Language (DML) operations.  

3. Enhancements in Oracle 23ai Version for Tablespace Management

The Tablespace Shrink feature is one of the significant innovations introduced with the Oracle database 23ai version. With this release, the long-awaited capability for DBAs to safely and effectively reclaim unused space in tablespaces has been directly integrated into the database core. Initially, this feature was primarily designed for Bigfile tablespaces. Bigfile tablespaces consist of a single large data file (supporting up to 4G blocks) and are intended to simplify the management of very large databases compared to traditional Smallfile tablespaces (which can contain multiple, smaller data files). The default creation of essential system tablespaces like SYSAUX, SYSTEM, and USER as Bigfile tablespaces in Oracle Database 23ai also indicates a trend in this direction.  

The scope of the feature was significantly expanded with a subsequent update to Oracle Database 23ai, Release Update 23.7 (and later). This update extended Tablespace Shrink functionality to support Smallfile tablespaces as well. This enhancement is of great importance, especially for organizations with legacy systems that use multiple small data files or for environments that prefer the Smallfile tablespace structure due to specific application requirements. Although the default tablespace type in Oracle 23ai is set to Bigfile, the option to create Smallfile tablespaces still exists, and this new development makes storage management for Smallfile tablespaces more flexible and efficient.  

Oracle’s strategy of first introducing a feature for more modern and relatively simpler data structures like Bigfile tablespaces, and then extending support to more traditional and potentially complex structures like Smallfile tablespaces, often reflects a phased rollout and maturation approach. Bigfile tablespaces, consisting of a single large data file, present less complexity for implementing and testing a shrink mechanism compared to Smallfile tablespaces, which involve the interaction of multiple data files. By first introducing a new and significant feature in this more controlled environment, Oracle aims to ensure stability and make improvements based on user feedback. Smallfile tablespaces, on the other hand, can contain multiple data files, and how these are managed during a shrink operation (e.g., consolidating data into fewer files, shrinking each file individually) can introduce additional operational complexities. Therefore, the introduction of Smallfile support with a Release Update (RU) after the feature’s initial debut has allowed for further maturation of the feature and its confident adoption by a broader user base.  

The delivery of Smallfile support via a Release Update like 23.7 also reflects Oracle’s “continuous innovation” model. Oracle delivers both bug fixes and new features and enhancements through RUs released between major database versions. The fact that Smallfile support for a significant feature like Tablespace Shrink came with an RU once again underscores the importance for users to keep their systems at current RU levels to benefit from such valuable developments. This also highlights the necessity for DBAs to regularly follow Oracle’s feature announcements, release notes, and support documentation.  

4. Advantages of Oracle 23ai Tablespace Shrink

The Tablespace Shrink feature in Oracle 23ai offers several tangible advantages to database administrators and, consequently, to organizations. These benefits range from storage efficiency to performance gains:

• Optimized Storage Utilization and Cost Reduction: The most fundamental and direct benefit is the increased disk space efficiency achieved by reclaiming “empty” space that accumulates in tablespaces over time and is not actively used. This idle space, resulting from deleted tables, updated data, or reorganized indexes, can be effectively returned to the operating system using the DBMS_SPACE.SHRINK_TABLESPACE procedure. This translates directly into financial savings, especially in large data environments where storage costs are a significant expense, or on cloud platforms where a pay-as-you-go model is prevalent. Many sources state that this feature “helps both reduce costs and optimize database storage” and “lowers total storage costs”. Oracle’s own promotions also state, “This feature allows you to reclaim unused space in the database, reducing costs and optimizing storage”.  
• Enhanced Query Performance: The Tablespace Shrink operation reduces data fragmentation by reorganizing and compacting segments. When logically related data blocks are also physically located in closer proximity on the disk, Oracle’s data access becomes faster. This leads to shorter completion times for I/O-intensive operations, particularly full table scans and wide-ranging index range scans. Fewer and more efficient I/O operations result in improved overall query response times. A case study reported that a 30% reduction in tablespace size led to a 25% improvement in query performance. Considering that fragmentation “increases data retrieval times and slows query performance” , this improvement is quite significant.  
• Faster Backup and Recovery Operations: When the total size of data files is reduced, the time required to back up these files (e.g., with RMAN) and restore them in a disaster scenario is also significantly shortened. Less data to read, process, and write contributes to more effective use of backup windows and improves critical business continuity metrics like Recovery Time Objectives (RTO) and Recovery Point Objectives (RPO). One study noted that the shrink operation “reduced backup times by 20%”.  
• Simplified Storage Management: Especially in Smallfile tablespaces composed of numerous small data files, the shrink operation (if it can consolidate data files) can create a structure with fewer, more organized data files. This simplifies overall file system management and tablespace maintenance tasks. While Bigfile tablespaces already have a single data file, making this advantage more pertinent to Smallfile environments, improving the internal organization of a Bigfile also indirectly contributes to manageability.  

These advantages are often interconnected and create a combined effect. For instance, reduced fragmentation (leading to performance gains) and smaller data file sizes (providing space savings) directly contribute to shorter backup times. The shrink operation consolidates free space by moving objects towards the beginning of the data file. This ensures that data that should be logically contiguous is also physically closer, reducing fragmentation. Reduced fragmentation allows queries to access data faster because fewer I/O operations and extent reads are required. Simultaneously, the total size of the data file decreases. Smaller data files mean that backup tools like RMAN need to read and write less data, which shortens backup times.  

The concept of “cost optimization” is not limited to direct disk space costs. It can also mean less administrative effort (thus reducing human resource costs) and potentially lower licensing costs (if some cloud services or third-party tools have licensing models based on storage size). While manual shrink operations or complex scripts require time and deep expertise , the DBMS_SPACE.SHRINK_TABLESPACE procedure greatly simplifies this process, saving DBAs valuable time.  

5. Working Mechanism of Tablespace Shrink in Oracle 23ai

The fundamental working principle of the Tablespace Shrink feature in Oracle 23ai is based on two main steps: first, reorganizing segments (tables, indexes, etc.) within the tablespace to consolidate free space; and second, physically shrinking the data file(s) at the end where this consolidated free space has accumulated, thereby returning disk space to the operating system.  

Object Movement (Online/Offline) and Its Core Logic: The first and most critical step of the procedure is to efficiently move various segments (tables, indexes, LOB segments, etc.) within the tablespace towards the beginning of the data file or files. This movement ensures that unused free spaces, scattered among data blocks and causing “fragmentation,” are gathered at the end of the data file(s). This creates a large, contiguous block of free space at the end of the data file that can then be shrunk.  

This object movement is performed via Oracle’s Data Definition Language (DDL) commands, either online or offline, depending on the shrink_mode parameter passed to the DBMS_SPACE.SHRINK_TABLESPACE procedure.  

• Online Move: When TS_SHRINK_MODE_ONLINE or TS_SHRINK_MODE_AUTO (in case of a successful online attempt) modes are selected, Oracle attempts to move objects while allowing DML (INSERT, UPDATE, DELETE) operations and queries on the respective objects. This is important for maintaining application availability. However, online operations generally consume more system resources (CPU, I/O, redo log) and can be slower than offline operations due to certain locking mechanisms.  
• Offline Move: When TS_SHRINK_MODE_OFFLINE or TS_SHRINK_MODE_AUTO (if the online attempt fails) modes are selected, objects are moved offline. This means access to the respective objects will be blocked during the move operation. However, offline movement is generally faster and can, in some cases, achieve more effective compaction.  

It is noted that for Smallfile tablespaces, these move operations trigger well-known DDL commands like ALTER TABLE <table_name> MOVE ONLINE and ALTER INDEX <index_name> REBUILD ONLINE in the background. This indicates that the Tablespace Shrink feature doesn’t work “magically” but rather intelligently orchestrates Oracle’s existing and proven DDL capabilities.  

Data File Resizing Process: After objects are successfully moved and unused free space is consolidated at the end of the data file(s), the second main step of the DBMS_SPACE.SHRINK_TABLESPACE procedure comes into play: reducing the physical size of the data file(s). This operation works with a logic similar to the ALTER DATABASE DATAFILE <datafile_name> RESIZE <new_size> command. However, unlike using this command directly, the Tablespace Shrink feature performs the object movement step beforehand, thus preventing the ORA-03297 error (file contains used data beyond requested RESIZE value). A successful object movement “cleans” the end of the data file, allowing the resize operation to be performed safely and the reclaimed free space to be physically returned to the operating system.  

The use of “Online DDL” demonstrates Oracle’s emphasis on uninterrupted operations and high availability. However, the term “online” does not always mean “zero impact.” While online DDL operations allow DML operations to continue , they perform intensive background tasks such as copying data blocks, logging, and metadata updates. These activities create an additional load on the system in terms of CPU, I/O, and redo log generation. Especially on heavily loaded systems, this extra load can temporarily affect overall database performance. Therefore, even in online mode, it is generally recommended to perform Tablespace Shrink operations during periods of lower system activity or within planned maintenance windows.  

In Smallfile tablespaces with multiple data files, the shrink operation may attempt to consolidate these files and reduce the number of data files. This aims not only to reclaim disk space but also to simplify file management. However, some observations suggest that “unlike bigfile tablespace shrink, smallfile tablespace shrink doesn’t appear to release much free space”. This might indicate that in Smallfile tablespaces, the priority is sometimes to reduce data files to a minimum number and then shrink the remaining files, rather than raw space reclamation. Especially in older systems with many small data files, this consolidation step can be considered a managerial gain, even if there isn’t a large reduction in the total number of used blocks.  

6. Deep Dive into DBMS_SPACE.SHRINK_TABLESPACE Procedure

The central tool for performing tablespace shrink operations in Oracle 23ai is the SHRINK_TABLESPACE PL/SQL procedure, located within the DBMS_SPACE package. This procedure supports both Bigfile tablespaces and, from Oracle 23ai Release Update 23.7 onwards, Smallfile tablespaces. It is primarily used for two main purposes: first, to analyze a tablespace before actually shrinking it, reporting potential space savings and movable objects (ANALYZE mode); and second, to directly perform the shrink operation (SHRINK modes).  

Parameters and Their Meanings: The DBMS_SPACE.SHRINK_TABLESPACE procedure accepts various parameters to control its behavior. A correct understanding of these parameters is crucial for the effective use of the feature.

Parameter Name	Data Type	IN/OUT	Default Value	Description
ts_name	VARCHAR2	IN	None	The name of the tablespace to be shrunk or analyzed.
shrink_mode	NUMBER	IN	DBMS_SPACE.TS_SHRINK_MODE_ONLINE	Determines the operating mode of the procedure. One of the following constants is used.
			DBMS_SPACE.TS_SHRINK_MODE_ANALYZE	Analyzes the tablespace, reports potential savings, but performs no shrink. (Formerly DBMS_SPACE.TS_MODE_ANALYZE)
			DBMS_SPACE.TS_SHRINK_MODE_ONLINE	Default mode. Attempts to move objects online (except Index-Organized Tables, which are moved offline). Aims for minimal downtime. (Formerly DBMS_SPACE.TS_MODE_SHRINK)
			DBMS_SPACE.TS_SHRINK_MODE_AUTO	First attempts an online move; if it fails, switches to an offline move. (Formerly DBMS_SPACE.TS_MODE_SHRINK_FORCE)
			DBMS_SPACE.TS_SHRINK_MODE_OFFLINE	Moves objects offline. Offers the best shrink result and performance in terms of processing time, but objects become inaccessible during the operation. (Distinct naming for 23.7 and later)
target_size	NUMBER	IN	DBMS_SPACE.TS_TARGET_MAX_SHRINK	Specifies the target size of the tablespace in bytes after shrinking. The default value aims to shrink to the smallest possible size.
shrink_result	CLOB	OUT	None (Optional)	An output parameter that returns the result of the operation as a CLOB (Character Large Object). In analyze mode, it contains movable objects, potential savings; in shrink modes, it includes the number of moved objects, old/new size, etc.

Note on the ITERATIONS Parameter: Although some community resources or blog posts mention an ITERATIONS parameter for the SHRINK_TABLESPACE procedure, Oracle’s official DBMS_SPACE package reference documentation does not list such a parameter for this procedure. The concept of ITERATIONS is more commonly associated with the Automatic SecureFiles Shrink feature. Therefore, it should be assumed that an ITERATIONS parameter is not a standard feature of DBMS_SPACE.SHRINK_TABLESPACE, and this information is likely a misunderstanding or pertains to a custom development environment. Users are always advised to refer to official Oracle documentation.  

Usage Scenarios and SQL Examples: Below are SQL examples demonstrating how the DBMS_SPACE.SHRINK_TABLESPACE procedure can be used in different scenarios. These examples assume DBMS_OUTPUT is used to view the results; the shrink_result CLOB parameter can be used to retrieve similar information programmatically.

• Analyze Mode Usage (To See Potential Savings):
  
  SQLSET SERVEROUTPUT ON; DECLARE v_result CLOB; BEGIN DBMS_SPACE.SHRINK_TABLESPACE( ts_name => 'USERS', shrink_mode => DBMS_SPACE.TS_SHRINK_MODE_ANALYZE, shrink_result => v_result ); DBMS_OUTPUT.PUT_LINE('---ANALYZE RESULT---'); DBMS_OUTPUT.PUT_LINE(v_result); END; /
  
  This command analyzes the ‘USERS’ tablespace and reports how much space can be saved and which objects can be moved, but does not perform any shrinking.  
  
• Default Shrink Mode (Online Move, Maximum Possible Shrink):
  
  SQLSET SERVEROUTPUT ON; DECLARE v_result CLOB; BEGIN DBMS_SPACE.SHRINK_TABLESPACE( ts_name => 'USERS', shrink_result => v_result -- shrink_mode and target_size take default values ); DBMS_OUTPUT.PUT_LINE('---SHRINK RESULT---'); DBMS_OUTPUT.PUT_LINE(v_result); END; /
  
  This command shrinks the ‘USERS’ tablespace in the default TS_SHRINK_MODE_ONLINE mode and with the TS_TARGET_MAX_SHRINK target size.  
  
• Shrinking to a Specific Target Size (Online Mode):
  
  SQLSET SERVEROUTPUT ON; DECLARE v_result CLOB; BEGIN DBMS_SPACE.SHRINK_TABLESPACE( ts_name => 'MYDATA', shrink_mode => DBMS_SPACE.TS_SHRINK_MODE_ONLINE, target_size => 100 * 1024 * 1024, -- 100 MB target size shrink_result => v_result );
DBMS_OUTPUT.PUT_LINE('---SHRINK RESULT---'); DBMS_OUTPUT.PUT_LINE(v_result); END; /
  
  This example aims to shrink the ‘MYDATA’ tablespace to approximately 100 MB in online mode.  
  
• AUTO Mode Usage (Try Online First, then Offline if Necessary):
  
  SQLSET SERVEROUTPUT ON; DECLARE v_result CLOB; BEGIN DBMS_SPACE.SHRINK_TABLESPACE( ts_name => 'USERS', shrink_mode => DBMS_SPACE.TS_SHRINK_MODE_AUTO, shrink_result => v_result ); DBMS_OUTPUT.PUT_LINE('---SHRINK RESULT (AUTO MODE)---'); DBMS_OUTPUT.PUT_LINE(v_result); END; /
  
• OFFLINE Mode Usage (For Best Shrink, if Downtime is Acceptable):
  
  SQLSET SERVEROUTPUT ON;
DECLARE v_result CLOB;
BEGIN DBMS_SPACE.SHRINK_TABLESPACE( ts_name => 'USERS', shrink_mode => DBMS_SPACE.TS_SHRINK_MODE_OFFLINE, shrink_result => v_result ); DBMS_OUTPUT.PUT_LINE('---SHRINK RESULT (OFFLINE MODE)---'); DBMS_OUTPUT.PUT_LINE(v_result); END; /

The presence of the shrink_result CLOB parameter allows for more detailed and structured retrieval of the operation’s results (often in JSON or XML format, though the documentation doesn’t specify the format), enabling these results to be programmatically processed by automation scripts or custom reporting tools. For example, within a PL/SQL block, this CLOB content can be parsed to extract metrics such as the number of objects moved, the amount of space reclaimed, and processing time. This information can then be saved to custom log tables, emailed to relevant personnel, or integrated into a monitoring system.

The introduction of the TS_SHRINK_MODE_AUTO mode demonstrates Oracle’s effort to provide an intelligent default behavior: “first try with minimal downtime and in the safest way; if the target cannot be reached with this method or some objects cannot be moved, then automatically switch to a more effective but potentially downtime-inducing method.” This somewhat reduces the burden on the database administrator to decide “online or offline?” for each object or situation. In this mode, Oracle first attempts an online move; if the online move fails for an object (e.g., the object type does not support online move, or the operation cannot be performed due to a lock), it then attempts an offline move for the same object. This is a “best-effort” approach aimed at balancing both availability and space reclamation.

7. Oracle Enterprise Manager (EM) Integration for Tablespace Shrink

Oracle Enterprise Manager (EM) is a comprehensive toolset for centrally managing, monitoring, and maintaining Oracle databases and other Oracle products. In the context of the Tablespace Shrink feature, EM can assist DBAs, particularly with analysis, visualization, and indirect monitoring capabilities.

Tablespace Occupancy and Content Analysis (Extent Map): EM Cloud Control offers powerful tools to visualize the current occupancy rates of tablespaces, the distribution of segments they contain, and, most importantly, where and how much free space (fragmentation) exists. Specifically, the “Extent Map” view graphically displays the status (in use, free) of each extent (space allocation unit) within a tablespace. Free spaces are typically colored green, and this map is very useful for understanding whether a shrink operation is needed and where potential savings are concentrated. DBAs can usually navigate to the relevant tablespace via the Administration -> Storage -> Tablespaces menu path in the EM interface, then select the tablespace and choose an option like “Show Tablespace Content” to access this detailed extent map. This visual analysis is an important step for assessing the situation before running the DBMS_SPACE.SHRINK_TABLESPACE procedure.  

Space Gain Analysis with Segment Advisor: Segment Advisor, an integral part of EM, is an automated tool that analyzes how much space can be gained by reorganizing or compressing tablespaces and individual segments (tables, indexes, etc.) within them. Segment Advisor evaluates the fragmentation level, number of empty blocks, and other metrics for specific segments, providing concrete recommendations on which segments would benefit most from a shrink operation. These recommendations, similar to the information provided by the TS_SHRINK_MODE_ANALYZE mode of the DBMS_SPACE.SHRINK_TABLESPACE procedure, can be used to assess space reclamation potential and plan the shrink strategy.  

Monitoring and Management of Shrink Operations via EM: Based on available research materials and Oracle documentation , there is no clear evidence that Enterprise Manager has the capability to initiate, schedule, or actively manage the DBMS_SPACE.SHRINK_TABLESPACE procedure directly through a graphical user interface (GUI) wizard, a dedicated button, or a menu option. Although EM typically integrates new PL/SQL-based management features into its GUIs over time, such direct integration does not appear to exist for this specific feature (at least according to the cited sources). Some inaccessible resources might contain more details, but this conclusion is based on the currently available information.  

However, this does not mean EM is entirely irrelevant to the shrink process. EM’s general database monitoring capabilities (e.g., performance monitoring pages, active sessions list, long-running SQL operations reports) can be used to indirectly monitor the progress and system impact (CPU usage, I/O activity, waits, etc.) of a DBMS_SPACE.SHRINK_TABLESPACE operation initiated via SQL*Plus, SQL Developer, or an automation script. For example, records related to the shrink operation in the V$SESSION_LONGOPS view (if Oracle flags this operation as a long-running one) might be reflected in EM’s performance monitoring interfaces.  

The absence of a direct “run” button or a dedicated management screen in EM for DBMS_SPACE.SHRINK_TABLESPACE might suggest that Oracle prefers such powerful operations—which can potentially be lengthy, resource-intensive, and even cause downtime—to be performed under the full and conscious control of the DBA, typically via scripts and with a full understanding of all parameters. GUIs can sometimes struggle to offer the user the full flexibility and control level provided by PL/SQL procedures. Therefore, Oracle might expect DBAs to execute these critical operations via SQL*Plus, SQL Developer, or automation scripts, carefully specifying all parameters and modes. In this scenario, EM primarily takes on the role of performing detailed analysis before these operations (via Segment Advisor, Extent Map) and monitoring the overall system status and performance during/after the operation.

Nevertheless, given Oracle’s trend of integrating new database features into Enterprise Manager GUIs over time, it is quite plausible that future EM versions or add-ons for database management packs will offer a more integrated management interface for the DBMS_SPACE.SHRINK_TABLESPACE feature.

8. Key Considerations and Limitations for Tablespace Shrink

While the Tablespace Shrink feature in Oracle 23ai offers significant advantages in storage management, understanding certain limitations and crucial considerations is necessary for its successful and trouble-free implementation.

Unsupported Object Types: The DBMS_SPACE.SHRINK_TABLESPACE procedure is not capable of moving all object types within a tablespace. When the procedure is run in TS_SHRINK_MODE_ANALYZE mode, the shrink_result CLOB output will include a list of objects that cannot be moved or are unsupported. For example, it has been noted that cluster tables and some Advanced Queuing (AQ) tables may not be supported by this procedure. Additionally, tables containing columns with the LONG data type, cluster tables, and tables with what Oracle terms “reservable columns” cannot be moved even in TS_SHRINK_MODE_OFFLINE (offline move) mode. In tablespaces containing such objects, the shrink operation may not complete fully as expected, or these objects may remain in their original locations.  

Impact on LOB Segments, Index-Organized Tables (IOTs), and Partitioned Tables: The behavior of these commonly used special object types during a shrink operation and the specific conditions they require are as follows:

Object Type	Behavior/Support with TS_SHRINK_MODE_ONLINE	Behavior/Support with TS_SHRINK_MODE_OFFLINE	Special Notes/Considerations
LOB (Large Object) Segments	Movable.	Movable.	Row movement (ROW MOVEMENT) must be enabled on the relevant tables for effective shrinking. Separate automatic shrink mechanisms also exist for SecureFiles LOBs (DBMS_SPACE.SECUREFILE_SHRINK_ENABLED()).
Index-Organized Tables (IOT)	Moved offline.	Moved offline.	Even if TS_SHRINK_MODE_ONLINE is selected, IOTs are moved offline, implying downtime for IOT access. Secondary index and mapping table segments cannot be shrunk individually; shrinking the primary segment affects them as well.
Partitioned Tables	Tablespace-level shrink affects all partitions within.	Tablespace-level shrink affects all partitions within.	The DBMS_SPACE package generally supports partitioned structures. The SHRINK_TABLESPACE procedure takes the tablespace name; it’s unclear if individual partitions can be directly shrunk with this procedure, likely requiring methods like ALTER TABLE... SHRINK SPACE PARTITION.
Cluster Tables	May not be supported / Unmovable.	Unmovable.	The ANALYZE mode result should be checked.
Advanced Queuing (AQ) Tables	Some AQ tables may not be supported / Unmovable.	Some AQ tables may not be supported / Unmovable.	The ANALYZE mode result should be checked.
Tables with LONG Data Type	Unmovable.	Unmovable.

Differences and Impacts of Online vs. Offline Move Modes:

• Online Modes (TS_SHRINK_MODE_ONLINE, online part of TS_SHRINK_MODE_AUTO): These modes aim to maximize application availability; DML operations and queries can continue while objects are being moved. However, this flexibility comes at a cost: background operations like data copying and index updating can create additional CPU, I/O, and redo log generation load on the system. Index-Organized Tables (IOTs) are moved offline even if this mode is selected. An important point is that online moves performed by SHRINK_TABLESPACE may not have all the restrictions of the traditional ALTER TABLE... MOVE ONLINE command; the ANALYZE mode will identify objects unsuitable for online move.  
• Offline Modes (TS_SHRINK_MODE_OFFLINE, offline part of TS_SHRINK_MODE_AUTO): These modes are generally faster and can provide better compaction (thus more space reclamation). This is because they avoid the extra work online modes do to ensure DML compatibility. However, the main disadvantage is that DML and query access to the moved objects is blocked during the operation, meaning downtime. When using TS_SHRINK_MODE_AUTO, it should be noted that if an online move attempt fails, the procedure will automatically switch to an offline move, which could lead to unexpected downtime.  

Characteristics of Shrink Operations on Smallfile Tablespaces: Shrinking Smallfile tablespaces can exhibit some different behaviors compared to Bigfile tablespaces. Observations suggest that Smallfile tablespace shrink operations might sometimes not reclaim as much free space as expected compared to Bigfile operations. This can occur especially when the goal is to consolidate multiple data files into a single file or when the data distribution is very complex. A single run of DBMS_SPACE.SHRINK_TABLESPACE might not consolidate all data files as desired; the operation might need to be repeated several times to achieve the desired level of consolidation. Additionally, it should be noted that during a shrink operation on Smallfile tablespaces, the sizes of some data files might temporarily increase and then decrease depending on the new layout of the objects within them.  

Potential Error Conditions and Partial Failure Scenarios: A Tablespace Shrink operation may not always complete with 100% success. The operation can partially fail; for example, if an object cannot be moved (due to being locked by another session, being an unsupported object type, etc.), the procedure might report an error for that object. However, thanks to other successfully moved objects, the data file might still be shrunk to some extent. A common error, $ORA-00054$: resource busy and acquire with NOWAIT specified or timeout expired, can occur if an object is locked by another session and the shrink operation cannot wait for this lock. If the operation is interrupted in some way (e.g., manually stopped by the DBA), online DDL operations completed up to that point (i.e., successfully moved objects) are not rolled back; these objects remain in their new locations, and a subsequent shrink attempt will benefit from this, meaning previous gains are preserved even if the operation doesn’t resume from where it left off.  

AUTOEXTEND Settings: If the data files of the tablespace to be shrunk are set to AUTOEXTEND OFF (automatic growth disabled), there might not be enough free space left in the tablespace for segments to grow after the shrink operation. In this case, the DBA might need to manually increase the tablespace or data file size to accommodate future data growth.  

Shrinking the SYSAUX Tablespace: The ability to shrink the SYSAUX tablespace—generally considered a system tablespace that houses important metadata like AWR (Automatic Workload Repository) data, statistics history, and scheduled job information—using the DBMS_SPACE.SHRINK_TABLESPACE procedure is a significant capability. This is particularly valuable in environments with limited storage space, such as Oracle Database Free, because the SYSAUX tablespace counts towards the 12 GB total user data limit in this edition and can grow over time, reducing the space available for application data.  

The statement “Online moves via SHRINK_SPACE don’t have all of the restrictions associated with a conventional ALTER TABLE… MOVE” implies that although the DBMS_SPACE.SHRINK_TABLESPACE procedure uses the ALTER TABLE... MOVE ONLINE command in the background , it might be able to bypass some of the known restrictions of this standard command or operate with a different internal logic. This suggests that Oracle might have developed special optimizations or different lower-level internal mechanisms for this new procedure. The fact that the ANALYZE mode provides a list of “unsupported objects” confirms that there are still some objects that even this special mechanism cannot move. This indicates that Oracle is pushing the boundaries of online operations, but some fundamental limits still apply.  

9. Performance Impacts and Evaluations of Tablespace Shrink

The performance of the Tablespace Shrink operation itself during its execution, as well as its potential effects on the overall operational performance of the database, are important evaluation criteria for DBAs.

Performance of the Shrink Operation Itself (Duration, Resource Usage): The tablespace shrink operation can be time-consuming, especially for very large tablespaces or when numerous or very large segments need to be moved. The completion time of the operation can vary greatly depending on the total amount of data to be moved, the number of segments, the selected shrink_mode (online modes are generally slower than offline modes), the overall load on the system, and the hardware capacity (especially I/O performance).  

The progress of the operation can be monitored using Oracle’s V$SESSION_LONGOPS dynamic performance view, which is designed for tracking long-running operations. By searching for an OPNAME like ‘%Table%’ or a similar identifier related to the shrink operation (e.g., Table/Index Maintenance) in this view, one can get information about the current phase of the operation, how much has been completed, and the estimated remaining time.  

As a general rule, the TS_SHRINK_MODE_OFFLINE (offline move) mode usually completes faster and potentially provides better compaction (more space reclamation) than the TS_SHRINK_MODE_ONLINE (online move) mode. The primary reason for this is that online modes use additional synchronization, lock management, and versioning mechanisms to allow DML (Data Manipulation Language) operations to continue during the process, which adds overhead and complexity. The offline mode, by exclusively locking the object, avoids these extra tasks and performs the move operation more directly.  

Potential Impacts on Overall Database Performance (CPU, I/O, Redo):

• Impact During Online Modes: When using TS_SHRINK_MODE_ONLINE or TS_SHRINK_MODE_AUTO (during its online phase), object movement operations can consume significant CPU and I/O resources in the background. Additionally, redo log generation will increase for every data block moved. This additional load, especially on already busy systems, can negatively affect the performance of other user sessions and application processes. The increase in redo log generation can increase log switch frequency, strain archiving processes, and potentially cause apply lag in physical standby database environments like Data Guard. Therefore, even when using online modes, it is highly recommended to perform such operations during periods of lower system load or within planned maintenance windows.
  
• Impact During Offline Modes: When using TS_SHRINK_MODE_OFFLINE or TS_SHRINK_MODE_AUTO (during its offline phase), there will be no direct DML-related performance issues for the moved objects, as access to them is completely blocked during the operation. However, the operation itself will still consume CPU and I/O resources. The main impact of this mode is that the affected objects become unusable until the operation is complete, which translates to application downtime.
  
• Post-Operation Performance: After a successful Tablespace Shrink operation, positive effects on database performance are generally expected. Reduced data fragmentation and the tighter, more organized packing of data on disk can enable query operations (especially full table scans and wide-ranging index range scans) to run with less I/O and therefore faster. This can lead to improvements in overall application response times.  

Upon reviewing available research materials and Oracle documentation , comprehensive benchmark results or official performance studies quantifying the specific impact of DBMS_SPACE.SHRINK_TABLESPACE in its different modes on performance metrics like CPU usage, I/O rates, and redo log generation are not found. Such detailed analyses are typically found in Oracle’s internal tests, beta programs, or specifically published white papers. The most accurate approach for users is to test this operation in their own environments, on test systems that mimic production, to observe its specific performance impacts.  

The statement “The best shrink result and performance is achieved with TS_SHRINK_MODE_OFFLINE” should be understood such that “performance” refers to the completion speed of the shrink operation and its space reclamation effectiveness, not the overall operational performance of the database. The offline mode, by not dealing with DML synchronization or the complexities of online DDL operations, generally takes less time to move and compress the same amount of data and can consolidate free space more effectively than online modes.  

10. Best Practices and Advanced Scenarios for Tablespace Shrink

To make the most of the Oracle 23ai Tablespace Shrink feature and minimize potential issues, it’s important to follow certain best practices and plan the operation carefully.

Pre- and Post-Shrink Checks:

• Pre-Operation Steps:
  1. Analysis: Run the DBMS_SPACE.SHRINK_TABLESPACE procedure in TS_SHRINK_MODE_ANALYZE mode to thoroughly examine the potential space savings, movable objects, non-movable (unsupported) objects, and the Oracle-recommended target size for the tablespace to be shrunk. This analysis will give a preliminary idea of whether the operation will be beneficial and how long it might take.  
  2. Visual Inspection (EM): Use Oracle Enterprise Manager (EM) to visually confirm the current occupancy rate of the tablespace, the distribution of segments, and especially the “Extent Map” view to see where fragmentation is concentrated and how free space is distributed.  
  3. Segment Advisor: Utilize the Segment Advisor tool within EM to perform a more detailed space gain analysis on individual segments within the tablespace and evaluate recommendations on which segments would benefit most from the shrink operation.  
  4. Backup: As with any major database operation, ensure that a current and verified backup of the tablespace to be processed, and ideally the entire database, has been taken. This provides a fallback option in case of unexpected issues (a general DBA best practice).
  5. AUTOEXTEND Settings: Check the AUTOEXTEND (automatic growth) settings of the data files belonging to the tablespace to be shrunk. If AUTOEXTEND OFF, there might not be enough free space left for segments to grow after the shrink; plan for manual size increases post-operation if necessary.  
  6. Resource Planning: Evaluate the potential impact of the operation on system resources (CPU, I/O) and plan to run it during a time of lower system load or within a planned maintenance window.
• Post-Operation Steps:
  1. Verification (Dictionary Views): Query Oracle data dictionary views like DBA_FREE_SPACE, DBA_DATA_FILES, and DBA_TABLESPACES to verify the changes in tablespace and data file sizes, the reclaimed free space, and the new high-water mark (HWM) levels.  
  2. Verification (EM): Re-examine the “Extent Map” view of the tablespace via EM to visually confirm that free space has been consolidated as expected and the data file has shrunk.
  3. Update Statistics: Since the shrink operation changes the physical layout of tables and indexes, it can cause statistics on these objects to become stale. For the Oracle Optimizer to generate accurate query execution plans, statistics for the relevant objects (especially moved tables and rebuilt or affected indexes) must be updated using the DBMS_STATS package after the operation. This step is critical for fully realizing the expected performance improvements.  
  4. Monitor Application Performance: Closely monitor the performance of relevant applications for a period after the shrink operation to observe any unexpected behavior or performance changes.
  5. Check Alert Log and Trace Files: Check the Oracle alert log and relevant trace files for any error or warning messages during or after the operation.

Mode Selection Based on Different Scenarios: The following table summarizes the key features of different shrink_mode options and in which scenarios they might be preferred:

Mode Name	Basic Function	Downtime Status	Performance (Operation Speed)	Space Gain Potential	Advantages	Disadvantages	Recommended Use Cases
TS_SHRINK_MODE_ANALYZE	Analyzes, reports, does not shrink.	None	N/A	N/A	See potential gain and problematic objects without risk.	Does not shrink.	For planning and evaluation before every shrink operation.
TS_SHRINK_MODE_ONLINE	Moves objects online (except IOTs).	Minimal (downtime for IOTs).	Medium	Good	High availability, DML continues.	Can be slower than offline, consumes more resources.	7/24 systems where application downtime is unacceptable.
TS_SHRINK_MODE_AUTO	Tries online first, then offline if fails.	Potential (if switches to offline).	Variable	Variable (depends on online/offline success)	Flexibility, automatic decision-making.	Can lead to unexpected downtime, exact mode used is not fully predictable.	Situations where the state is uncertain, or both availability and good shrinkage are desired, but controlled downtime is acceptable.
TS_SHRINK_MODE_OFFLINE	Moves objects offline.	Yes (for moved objects).	High	Best	Fastest operation, best compaction.	Access to relevant objects blocked during operation.	Planned maintenance windows, situations where downtime is acceptable and maximum space gain is targeted.

Export to Sheets

Maintenance Windows and Scheduling Recommendations: The Tablespace Shrink operation, especially when run on large tablespaces or when many objects need to be moved, can consume significant system resources and take a long time. Therefore, it is highly recommended that the operation (even when using online modes like TS_SHRINK_MODE_ONLINE) be performed preferably during times of lowest overall system load or within planned maintenance windows. This approach minimizes the potential performance impact of the shrink operation on other critical application processes and allows sufficient time to intervene and resolve any unexpected issues (e.g., the operation taking much longer than expected or stopping due to an error).  

Advanced Scenarios (Inferential):

• Phased Shrinking of Very Large Tablespaces: Instead of shrinking a very large tablespace (terabytes in size) in a single operation, dividing the process into several phases using the target_size parameter can be a more controlled approach. For example, a shrink of 10-20% of the tablespace could be targeted in each step. This allows for closer monitoring of the system impact of each phase, makes the operation duration more manageable, and reduces potential risks.
  
• Scripting and Automation: The DBMS_SPACE.SHRINK_TABLESPACE procedure can be easily automated via PL/SQL scripts. In particular, the shrink_result CLOB output can be programmatically processed to log information such as the success of the operation, reclaimed space, and the number of objects moved, or to report these to DBAs. For instance, an automation framework could be established to periodically analyze all tablespaces with TS_SHRINK_MODE_ANALYZE, automatically shrink tablespaces where potential space savings above a certain threshold (e.g., more than 20%) are detected (perhaps first with TS_SHRINK_MODE_ONLINE), or at least send an alert/notification to the DBA.
  
• Integration with Information Lifecycle Management (ILM): In environments where Information Lifecycle Management (ILM) strategies are implemented, the Tablespace Shrink feature can play a significant role. For example, tablespaces containing old data that is archived or completely deleted from the system after a certain period can be regularly subjected to a shrink operation after this data is cleared. This allows the valuable disk space occupied by these tablespaces to be reclaimed, keeping storage efficiency consistently high.

Finally, a point frequently emphasized in Oracle community forums and user experiences is the importance of testing such new features first in non-production (test, development) environments with data similar in size and structure to production data. Tests conducted with small-sized test data may not always accurately reflect the feature’s behavior, potential performance impacts, or possible bottlenecks in large-scale production systems. Therefore, comprehensive testing is indispensable for a successful production implementation.  

11. Conclusion and Evaluation of Oracle 23ai Tablespace Shrink

The Tablespace Shrink feature introduced with Oracle 23ai is a groundbreaking innovation for reclaiming unused space in tablespaces, addressing a significant long-standing challenge for Database Administrators (DBAs). This feature offers the potential to use storage resources more efficiently, thereby reducing costs and potentially enhancing system performance.

The introduction of the feature for Bigfile tablespaces in the initial release of Oracle 23ai, followed by its extension to Smallfile tablespaces with Release Update 23.7, demonstrates Oracle’s commitment to this functionality and its responsiveness to user needs. The flexible operating modes (TS_SHRINK_MODE_ANALYZE, TS_SHRINK_MODE_ONLINE, TS_SHRINK_MODE_AUTO, TS_SHRINK_MODE_OFFLINE) and control parameters like target_size offered through the DBMS_SPACE.SHRINK_TABLESPACE procedure provide DBAs with the means to perform shrink operations in a manner best suited to their environment’s requirements and downtime tolerances.

Oracle Enterprise Manager (EM), with tools like “Extent Map” and “Segment Advisor,” offers valuable contributions for performing detailed analysis before a shrink operation and for visualizing the state of tablespaces. During and after the operation, EM’s general monitoring capabilities can help track the impact on the system.

However, for the effective and safe use of the Tablespace Shrink feature, awareness of certain limitations and considerations is critically important. The existence of unsupported object types, the different behaviors of special object types like Index-Organized Tables (IOTs) and LOB segments during the shrink process, the impacts of online and offline modes on performance and availability, the unique shrink dynamics of Smallfile tablespaces, and potential error scenarios must be carefully evaluated by DBAs.

In conclusion, the Oracle 23ai Tablespace Shrink feature, when implemented with proper planning, comprehensive testing, and adherence to best practices, is an extremely powerful and valuable tool for DBAs to significantly improve database storage efficiency, reduce unnecessary storage costs, and enhance system performance. Continued developments by Oracle in this area may bring future innovations such as support for more object types, more advanced automation capabilities, or more comprehensive and direct management interfaces via EM. This feature stands out as one of Oracle’s innovative responses to modern data management challenges.