Table Management Enhancements for Oracle DBAs

Bigfile tablespaces — now the default in Oracle 23ai for SYSTEM, SYSAUX, and USER tablespaces — can be shrunk using DBMS_SPACE.SHRINK_TABLESPACE. The procedure compacts used segments toward the head of the datafile and trims unused space from the tail, reclaiming storage without manual segment moves or datafile resizing. Support for smallfile tablespaces was added in Oracle 23.7.

Three operating modes are available:

• TS_MODE_ANALYZE — reports potential savings and identifies movable objects without making any changes. Use this mode to assess a tablespace before committing to a shrink.
• TS_MODE_SHRINK (default) — performs the shrink, relocating segments online where possible.
• TS_MODE_SHRINK_FORCE — attempts offline relocation for segments that cannot be moved online.

EXEC DBMS_SPACE.SHRINK_TABLESPACE('RECLAIM_TS',
  shrink_mode   => DBMS_SPACE.TS_MODE_ANALYZE,
  shrink_result => :result_clob);

Running in TS_MODE_ANALYZE first is recommended practice — it shows what space would be reclaimed and which objects would be moved before any I/O-intensive work begins. The procedure can be scripted or scheduled as a routine maintenance task, making proactive space reclamation straightforward compared to the manual ALTER TABLE MOVE and datafile resize workflows it replaces.

Direct-path loads into tables using Hybrid Columnar Compression (HCC) previously incurred compression overhead during the load itself, slowing ingestion. Automatic Storage Compression addresses this by loading data uncompressed at full direct-path speed, then compressing it in the background after a DML inactivity threshold is reached.

Configuration requirements:

• Enable at the PDB level using DBMS_ILM_ADMIN.ENABLE_AUTO_OPTIMIZE.
• Requires HEAT_MAP=ON to track data access patterns.
• Target tables must use HCC in an AUTOALLOCATE, ASSM tablespace.

A background AutoTask monitors heat-map activity and compresses eligible segments once they reach the inactivity threshold. Progress can be monitored through V$SYSSTAT using the "Auto compression data moved" statistic.

This feature is particularly valuable for data warehouse and large ETL workloads where load throughput and long-term storage efficiency are both priorities.

SecureFiles — the default LOB storage format since Oracle 11g — can accumulate unused space as LOB data is updated or deleted. Oracle 23ai adds both manual and automatic shrink capabilities for SecureFiles LOB segments.

Manual shrink:

ALTER TABLE t MODIFY LOB (lob_col) SHRINK SPACE;
ALTER TABLE t SHRINK SPACE CASCADE;

Both statements run online with minimal blocking, making them suitable for production environments during maintenance windows.

Automatic shrink: A background task evaluates LOB segments based on idle time and free-space thresholds, then reclaims space gradually with low I/O impact. Enable with DBMS_SPACE.SECUREFILE_SHRINK_ENABLED(). This capability is not enabled by default in on-premises deployments.

Automatic SecureFiles Shrink applies to out-of-line SecureFiles LOBs only. Inline LOBs and BasicFiles LOBs are not eligible.

Oracle 23ai makes bigfile tablespaces the default for SYSTEM, SYSAUX, and USER tablespaces. A bigfile tablespace contains a single datafile that can grow to 32 TB or more, eliminating the multi-datafile management overhead of smallfile tablespaces for large databases. Combined with DBMS_SPACE.SHRINK_TABLESPACE, bigfile tablespaces can be both expanded and contracted through straightforward API calls rather than manual datafile operations.

Additional Oracle 23ai storage features that complement bigfile tablespace management:

• Advanced Index Compression LOW for Index-Organized Tables (IOTs) — reduces storage consumption for IOT primary key indexes.
• Partitioned Hybrid Tables — allow a single table to span multiple storage tiers (in-memory, disk, external storage such as HDFS or cloud object storage) using partition-level storage attributes and Automatic Data Optimization (ADO) tiering policies.
• ADO Policy Enhancements — improved lifecycle management policies for compression, tiering, and storage class transitions across pluggable databases.

Before these enhancements, reclaiming space in a fragmented tablespace required manual steps: identifying candidate segments, executing ALTER TABLE MOVE or ALTER INDEX REBUILD for each object, and resizing datafiles individually. LOB space reclamation required monitoring segment sizes through data dictionary views and running shrink commands manually. HCC load performance required choosing between fast uncompressed loads and space-efficient compressed storage.

The Oracle 23ai enhancements address each of these pain points. DBAs can now analyze and reclaim tablespace space with a single procedure call, schedule LOB shrink operations as background tasks, and achieve both fast direct-path load performance and HCC compression without manual intervention.

These features integrate with Oracle Enterprise Manager for monitoring and can be scripted for scheduled execution. As with any operation that involves segment moves or background I/O, testing in a non-production environment before deploying to production is recommended.