Managing LOB Storage Size with DBMS_LOB and LOB Storage Clauses

LOBs (BLOB, CLOB, and NCLOB) are designed to hold large values, but “large” can be expensive if you don’t manage it. In Oracle 23ai, you control LOB storage size and performance in two complementary ways:

1. Physical storage attributes (how Oracle stores and caches LOB data on disk and in memory).
2. Programmatic LOB manipulation (how your code copies, appends, trims, or erases LOB contents).

This lesson focuses on both, but with an emphasis on keeping the topic contiguous: first you’ll see the DDL clauses that shape LOB storage, then you’ll see the DBMS_LOB procedures that change the size (or apparent size) of LOB values in-place.

1) Storage clauses that influence LOB size and I/O

When you create or alter a table, the LOB storage clause controls the physical characteristics of a LOB segment. These settings have a direct impact on space usage, redo/undo behavior, and the amount of data Oracle moves per I/O.

Oracle can store small LOB values inside the table row (inline) and move larger values to a dedicated LOB segment (out-of-line). The main knob is:

• ENABLE STORAGE IN ROW — allow inline storage when the value is small enough
• DISABLE STORAGE IN ROW — force out-of-line storage

CREATE TABLE t_lobs (
  id     NUMBER PRIMARY KEY,
  notes  CLOB
)
LOB (notes) STORE AS SECUREFILE (
  ENABLE STORAGE IN ROW
);

The important tuning idea: inline LOBs can reduce pointer chasing for small values, but they also make rows larger. Larger rows increase logical I/O for full scans and can increase buffer cache pressure.

CHUNK size

CHUNK defines the allocation and I/O unit used by the LOB segment (the size of pieces Oracle reads/writes). For write-heavy workloads, chunk size influences how efficiently Oracle can stream changes.

ALTER TABLE t_lobs
  MODIFY LOB (notes)
  (CHUNK 8192);

RETENTION vs PCTVERSION

LOB read consistency can require older versions of LOB data. Historically that was managed with PCTVERSION. Modern guidance is to use RETENTION (including RETENTION AUTO) so Oracle can make smarter decisions based on undo and workload behavior.

ALTER TABLE t_lobs
  MODIFY LOB (notes)
  (RETENTION AUTO);

Practical takeaway: when LOB churn is high (frequent updates to LOB content), retention settings and undo pressure become part of “LOB size management,” because older versions consume space until they can be released.

CACHE, NOCACHE, and buffer behavior

LOB caching determines whether LOB data is kept in the database buffer cache. This matters when LOBs are read often and are small enough to benefit from caching, or when caching them would evict more valuable blocks.

ALTER TABLE t_lobs
  MODIFY LOB (notes)
  (NOCACHE);

In Oracle’s modern LOB implementation (SecureFiles), you can trade CPU for reduced storage via compression and deduplication, and you can protect data at rest with encryption. These can materially change LOB storage size.

ALTER TABLE t_lobs
  MODIFY LOB (notes)
  (STORE AS SECUREFILE (
     DEDUPLICATE
     COMPRESS HIGH
     ENCRYPT
   ));

Size management point: compression and deduplication reduce disk usage, but can add CPU and may not benefit already-compressed payloads (e.g., JPEG images). Always validate on representative data.

Placing LOB segments in a dedicated tablespace

A classic best practice is to isolate LOB segments into their own tablespace for capacity planning and I/O management. This doesn’t shrink a LOB by itself, but it makes growth predictable and reduces fragmentation side effects on the base table.

ALTER TABLE t_lobs
  MODIFY LOB (notes)
  (TABLESPACE lob_tbs);

2) DBMS_LOB procedures that directly affect size (or effective size)

Common DBMS_LOB calls used in size workflows

Procedure	Why it matters for size	Key behavior
DBMS_LOB.COPY	Build or rebuild a LOB by copying only the needed portion	Supports offsets and byte counts; can overwrite a region in a destination LOB
DBMS_LOB.APPEND	Grow a LOB incrementally (log bodies, report assembly, document composition)	Concatenates at the end; does not replace existing content
DBMS_LOB.TRIM	Reduce the length of a LOB value (true shrink of the value)	Changes the LOB length to the new value (must be ≤ current length)
DBMS_LOB.ERASE	Clear bytes from a region without changing LOB length	Erases content but generally does not reduce length; space reclamation is different from TRIM

The next sections align with the figures on this page so the narrative stays continuous: COPY, APPEND, TRIM, then ERASE and the “space is not reclaimed” nuance.

DBMS_LOB.COPY: Copy specific bytes into a target LOB

DBMS_LOB.COPY is often used during controlled migrations, normalization routines, or when you want to rebuild a LOB while skipping unwanted regions. Because you specify offsets and the number of bytes, COPY is a precise tool.

DBMS_LOB.COPY(
  dest_lob,      -- destination locator
  src_lob,       -- source locator
  amount,        -- number of bytes/chars to copy
  dest_offset,   -- start position in destination
  src_offset     -- start position in source
);

DBMS_LOB.APPEND: Grow a destination LOB by concatenation

DBMS_LOB.APPEND is a common pattern for assembling a large CLOB/BLOB in steps. Conceptually, this is “dest := dest || src”. From a size perspective, APPEND is a deliberate growth mechanism.

DBMS_LOB.APPEND(dest_lob, src_lob);

DBMS_LOB.TRIM: Reduce the LOB length to a specific size

DBMS_LOB.TRIM is the canonical “make it smaller” operation. If you need a hard maximum size, or you want to remove trailing content permanently, TRIM changes the LOB length itself.

DBMS_LOB.TRIM(lob_locator, newlen);

DBMS_LOB.ERASE: Clear a region without shrinking length

DBMS_LOB.ERASE removes data from a region inside the LOB, but it does not behave like TRIM. The key concept (and the reason this belongs in a “size management” lesson) is that ERASE can leave the LOB at the same length while clearing content inside it.

DBMS_LOB.ERASE(lob_loc, amount, offset);

Putting it together: practical “LOB size” strategies

In real systems, “LOB size management” usually means combining DDL storage choices with a small set of safe, repeatable LOB manipulation patterns:

1. Keep small LOBs inline only when that matches access patterns.
   Inline can improve point reads, but can degrade scans and cache behavior if rows balloon.
2. Use SecureFiles compression/deduplication where it fits the payload.
   Text CLOBs often compress well; already-compressed binaries often do not.
3. Prefer TRIM when you must truly shrink a LOB value.
   ERASE is not a substitute for TRIM.
4. Use COPY/APPEND for controlled rebuilds.
   This is useful when you need to reshape content, stitch sections, or migrate data between columns/tables.
5. Plan for undo/read-consistency behavior with RETENTION.
   LOB updates can generate additional overhead; retention choices help control how long old versions must remain available.

In the next lesson, you will learn how Oracle uses LOBs to store large arrays (for example, VARRAYs stored as LOBs), which inherits many of the same storage and sizing principles.