Handling LOB Bloat in Oracle: SecureFiles & Online Reorganization

The Context: "The 10TB Storage Ghost"

It was mid-2023. Our "Document Store" table, which holds scanned KYC (Know Your Customer) PDFs for 5 million bank accounts, was taking up 12TB of space. Curiously, our calculations showed the actual data was only around 4TB.

This is the classic "LOB Bloat" problem. Oracle's Large Objects (LOBs) are notoriously difficult to reclaim space from once they grow. For 24 hours, the DB was near-locked as we hit the storage limit, despite having deleted 60% of the records. This article explores the architecture of SecureFiles LOBs and how we reclaimed 8TB of "Ghost" space without a single second of application downtime.

The Problem Statement

In a high-scale banking environment (10TB+), data deletion does not equal space reclamation for LOBs.

Goal: Reclaim storage and reduce index/segment bloat.
Metric: 100% Online Reorganization. No blocking locks on the production table.
Challenge: The "HWM (High Water Mark)". A LOB segment never drops its HWM unless significantly reorganized.

Visual Architecture: Basic LOB vs. SecureFiles Compression

The Core Logic: The Reclaim Process

1. The Migration to SecureFiles

If you're still on BasicFiles, you've already lost the battle. SecureFiles (introduced in Oracle 11g and matured in 19c/21c) is a completely different architecture that handles LOBs like filesystems.

Advantage: DEDUPLICATE and COMPRESS HIGH can reduce storage by 40-70% for repeat documents (like blank form templates).

2. The Online Reorganization (DBMS_REDEFINITION)

You cannot use ALTER TABLE ... SHRINK SPACE for LOB segments effectively. Instead, we use the DBMS_REDEFINITION package.

Process:
1. Create an empty "Interim" table with the desired SecureFiles storage parameters.
2. Start the redefinition: This begins a background copy of the data.
3. Sync the interim table with ongoing production changes (using a snapshot log).
4. "Finish" the redefinition: Oracle performs a metadata swap. Your Interim table becomes the Primary table.

3. Understanding UNDO and RETENTION

LOBs have their own "Mvcc" (Multi-Version Concurrency Control). If you set RETENTION, Oracle keeps old versions of the LOB in the segment for a specific time. If this is too high, your segment will "Bloat" with old versions that are no longer needed.

The "Baigan" Take: Insights from the Trenches

Look, people love to talk about "Cloud-first storage" and "S3 integration". But baigan ka logic hai wo (that's nonsense) if your core banking compliance requires the documents to be inside the ACID-compliant Oracle database.

The Index Bloat Trap. When you have a 10TB LOB table, people forget that most of the lookups are actually on the ACCOUNTS or TX_ID index. If your LOBs are bloated, the index becomes fragmented as well.

Rule: Never run a "Shrink" during business hours. It generates massive UNDO logs and will kill your I/O.
Rule: If you delete a LOB, the space is only marked as "EXPIRED" after the retention period. You won't see "FREE" space in DBA_SEGMENTS until those chunks are actually reused or reorganized.

Performance & Implementation Checklist

SecureFiles Storage: Ensure your LOB columns are defined with STORE AS SECUREFILE.
LOB Indexing: Monitor the LOBINDEX. If it's larger than 10% of the LOB data, you have a metadata problem.
Parallel Redefinitions: When using DBMS_REDEFINITION, enable parallel execution: ALTER SESSION FORCE PARALLEL DML.
Temp Tablespace: Reorganizing a 10TB table requires a massive amount of TEMP space for the initial copy and sorts. Settle for at least 200GB of TEMP for large LOB moves.

Conclusion

LOB Bloat is a silent system killer. It doesn't crash the app; it slowly drains your storage budget and degrades your backup/recovery times. By migrating to SecureFiles and mastering DBMS_REDEFINITION, you take control of your data footprint and ensure your database is as "lean" as it is large. Reclaim your space, optimize your I/O, and keep your production online.

Verified for Technical Nexus v1.9.0 | Part of the System Design Vault archive.