Locally Managed Tablespaces, ASSM, High-Water Marks, and Space Reclamation

Learning outcomes

ServiceHub deletes a large batch of old probe rows, yet the datafile size on disk does not fall. Someone concludes that Oracle “forgot to free space” and proposes shrinking every file nightly. The mechanism is different: row deletion frees row/block space for reuse, but segment extents and the datafile remain allocated until specific segment/tablespace/file operations release or move that allocation. This lesson separates locally managed extent tracking, Automatic Segment Space Management (ASSM), the high-water mark, and actual reclamation.

1. Locally managed tablespaces track extents with bitmaps

Modern Oracle tablespaces are locally managed: allocation metadata for extents is represented with bitmaps in the tablespace rather than dictionary-managed free-space bookkeeping. AUTOALLOCATE lets Oracle choose extent sizes; UNIFORM uses a fixed extent size. This policy answers “how are extents allocated?” It does not answer “how is free room inside each data block managed?”

SELECT tablespace_name, extent_management, allocation_type,       segment_space_management, bigfileFROM   dba_tablespacesWHERE  contents = 'PERMANENT'ORDER  BY tablespace_name;

Automatic Segment Space Management (ASSM) is the block-level free-space management mechanism typically used with locally managed tablespaces. ASSM uses bitmap structures to track blocks by available-space categories and reduces the need for manual free-list management. Do not conflate ASSM with ASM: ASSM manages free space inside database segments; Oracle ASM manages storage devices/disk groups below database files.

2. The high-water mark describes how far the segment has used blocks

The high-water mark (HWM) identifies the boundary below which blocks have been formatted/used by a segment. A full table scan can need to examine blocks up to the relevant high-water boundary even after many rows are deleted. Deleting rows frees row space for reuse, but it does not normally deallocate every extent or move the HWM downward.

That is why two statements can both be true: “the table contains far fewer rows” and “the segment still has roughly the same allocated bytes.” Reusable space inside the segment is valuable—it can absorb future inserts—but it is different from releasing extents back to the tablespace or reducing the datafile size.

3. Build a controlled probe and measure its allocation

As SERVICEHUB_OWNER, first confirm the target tablespace uses ASSM. Then create a moderate probe table and gather its segment allocation. The probe is intentionally isolated from course business tables.

SELECT tablespace_name, segment_space_managementFROM   user_tablespacesWHERE  tablespace_name = (  SELECT default_tablespace  FROM   user_users);CREATE TABLE servicehub_space_probe ASSELECT LEVEL AS probe_id,       RPAD('z', 240, 'z') AS payloadFROM   dualCONNECT BY LEVEL <= 40000;SELECT segment_name, tablespace_name, bytes, blocks, extentsFROM   user_segmentsWHERE  segment_name='SERVICEHUB_SPACE_PROBE';

If the default tablespace reports MANUAL instead of AUTO, do not run the shrink portion as written. The current Free/DBCA path should use ASSM, but the lesson verifies rather than assumes.

4. DBMS_SPACE makes the HWM boundary observable

DBMS_SPACE.UNUSED_SPACE reports total segment allocation and unused space above the high-water boundary for supported segment types. The values are not the same as “rows that could fit in partially empty blocks.” Use them to understand the segment boundary, not as a replacement for application data-volume metrics.

SET SERVEROUTPUT ONDECLARE  l_total_blocks NUMBER;  l_total_bytes NUMBER;  l_unused_blocks NUMBER;  l_unused_bytes NUMBER;  l_last_file_id NUMBER;  l_last_block_id NUMBER;  l_last_block NUMBER;BEGIN  DBMS_SPACE.UNUSED_SPACE(    segment_owner             => USER,    segment_name              => 'SERVICEHUB_SPACE_PROBE',    segment_type              => 'TABLE',    total_blocks              => l_total_blocks,    total_bytes               => l_total_bytes,    unused_blocks             => l_unused_blocks,    unused_bytes              => l_unused_bytes,    last_used_extent_file_id  => l_last_file_id,    last_used_extent_block_id => l_last_block_id,    last_used_block           => l_last_block  );  DBMS_OUTPUT.PUT_LINE('total blocks=' || l_total_blocks);  DBMS_OUTPUT.PUT_LINE('unused blocks above HWM=' || l_unused_blocks);  DBMS_OUTPUT.PUT_LINE('last used file=' || l_last_file_id || ', block=' || l_last_block);END;/

Run the measurement before deletion and save it. Exact numbers vary with block size, extent allocation, row format, and the Free image. Do not publish the sample’s local block counts as a universal Oracle benchmark.

5. Delete rows and watch the segment stay allocated

DELETE FROM servicehub_space_probeWHERE  probe_id <= 35000;COMMIT;SELECT COUNT(*) AS rows_remainingFROM   servicehub_space_probe;SELECT bytes, blocks, extentsFROM   user_segmentsWHERE  segment_name='SERVICEHUB_SPACE_PROBE';

The row count drops dramatically. Segment bytes commonly remain allocated because the extents are still owned by the segment and can be reused. The containing datafile size also remains unchanged. This is expected behavior, not evidence of corruption or a “leak.”

6. Deliberate failure: shrink without enabling row movement

Online segment shrink compacts rows, lowers the HWM, and can release space. Because rows may move to different rowids, Oracle requires row movement for ordinary heap-table shrink. The safe teaching failure is to try the shrink on the disposable probe before enabling row movement.

-- Expected to fail for a heap table when row movement is disabled.ALTER TABLE servicehub_space_probe SHRINK SPACE;-- Repair on the disposable lab table only.ALTER TABLE servicehub_space_probe ENABLE ROW MOVEMENT;ALTER TABLE servicehub_space_probe SHRINK SPACE COMPACT;ALTER TABLE servicehub_space_probe SHRINK SPACE;SELECT bytes, blocks, extentsFROM   user_segmentsWHERE  segment_name='SERVICEHUB_SPACE_PROBE';

The first statement should produce the documented row-movement requirement error in this scenario. The repair is not “grant more privileges” or “resize the file.” It is to acknowledge that rowids can change, enable row movement on an object where that is acceptable, compact the segment, and then release reclaimable extents.

7. Shrink, MOVE, index rebuild, and tablespace shrink solve different problems

Current 26ai documentation includes DBMS_SPACE.SHRINK_TABLESPACE for tablespace-level reorganization and file reduction. That does not make nightly global shrink a good policy. Moving objects creates I/O, redo, rowid/index/availability considerations, and may simply cause future regrowth.

8. Reclaimable inside Oracle is not automatically reclaimable by the OS

After segment shrink, released extents become free inside the tablespace. To return disk space to a filesystem, the relevant free region must allow the datafile to be resized or a tablespace-level operation must move/reorganize allocations. Free extents scattered below the physical end of a file do not guarantee that the file can be shortened to the sum of used bytes.

That is why “datafile is 10 GB and segments total 3 GB, therefore resize to 3 GB” is unsafe. The highest allocated block matters. In production, use supported space-analysis APIs/views and a tested rollback/recovery plan before resizing.

9. Hands-on cleanup and verification

SELECT COUNT(*) AS rows_remainingFROM   servicehub_space_probe;SELECT bytes, blocks, extentsFROM   user_segmentsWHERE  segment_name='SERVICEHUB_SPACE_PROBE';DROP TABLE servicehub_space_probe PURGE;SELECT COUNT(*) AS segment_rows_after_dropFROM   user_segmentsWHERE  segment_name='SERVICEHUB_SPACE_PROBE';

After the drop, the probe segment disappears. The datafile still need not shrink. That final observation is the core lesson: object deletion releases database allocation for reuse; lower-layer capacity reclamation is a separate administrative decision.

10. Production judgment and bridge

Do not schedule shrink because a dashboard shows “unused bytes.” Ask whether the application will reuse the space, whether scan cost or backup/storage capacity is actually impaired, whether row movement is safe, whether indexes/LOBs/partitions need separate treatment, and whether the operation’s I/O/redo/locking window is acceptable. Measure before and after with object-, tablespace-, file-, and storage-level evidence.

Lesson 4 moves one layer lower. Oracle ASM can replace filesystem-style placement decisions with disk groups, allocation units, striping, mirroring, failure groups, and rebalancing—but it is a separate Grid Infrastructure storage layer, not another name for ASSM or a feature a single Free container can meaningfully simulate.

Authoritative references

• Oracle AI Database Administrator’s Guide — Managing Space for Schema Objects — ASSM, high-water marks, segment shrink, and row movement
• Oracle AI Database Administrator’s Guide — Managing Tablespaces — locally managed tablespaces and tablespace shrink
• Oracle AI Database PL/SQL Packages — DBMS_SPACE — UNUSED_SPACE and space-analysis APIs
• Oracle AI Database SQL Language Reference — ALTER TABLE — row movement and segment shrink syntax
• Oracle AI Database Concepts — Logical Storage Structures — extent/segment/block space model