Large Objects   «Prev  Next»

Lesson 5Managing LOB size
ObjectiveUse LOB commands to adjust the storage size of LOBs

LOB commands to use to adjust the storage size of LOBs in Oracle 23ai

In Oracle 23ai, to adjust the storage size of LOBs (e.g., those used for large `VARRAY`s, `CLOB`, `BLOB`, or `NCLOB` data types), you use LOB storage clauses within the `CREATE TABLE` or `ALTER TABLE` statements.
Here are the main LOB storage commands and clauses to control LOB size, chunking, compression, deduplication, and inlining:
  1. ENABLE|DISABLE STORAGE IN ROW
    • Controls whether the LOB data is stored inline (within the table row) or out-of-line (in a separate LOB segment).
    • CREATE TABLE my_table (
  id NUMBER,
  my_lob CLOB
) LOB (my_lob) STORE AS (
  ENABLE STORAGE IN ROW
);
  2. CHUNK
    • Specifies the LOB chunk size (in bytes), which affects how LOBs are read/written in pieces.
    • LOB (my_lob) STORE AS (
  CHUNK 8192
);
  3. PCTVERSION
    • Controls the amount of space reserved for LOB versioning (for read consistency).
    • LOB (my_lob) STORE AS (
  PCTVERSION 10
);
    • Deprecated in favor of RETENTION, but still supported.
  4. RETENTION / RETENTION AUTO / RETENTION MAX / RETENTION MIN
    • Recommended alternative to PCTVERSION in modern versions (like 23ai).
    • LOB (my_lob) STORE AS (
  RETENTION AUTO
);
  5. CACHE | NOCACHE | CACHE READS
    • Controls how Oracle caches LOB data in the buffer cache.
    • LOB (my_lob) STORE AS (
  NOCACHE
);
  6. SECUREFILE and BASICFILE
    • SECUREFILE is the modern default in Oracle 23ai for better performance and advanced features.
    • LOB (my_lob) STORE AS SECUREFILE (
  DEDUPLICATE
  COMPRESS HIGH
  ENCRYPT
);
  7. TABLESPACE
    • You can define a separate tablespace for the LOB data:
    • LOB (my_lob) STORE AS (
  TABLESPACE lob_tbs
);
  8. Using ALTER TABLE to Modify LOB Storage 
    • ALTER TABLE my_table MODIFY LOB (my_lob) 
  (CHUNK 8192 
   RETENTION AUTO 
   NOCACHE 
   ENABLE STORAGE IN ROW);

📌 Summary Table
Option Purpose
CHUNK Controls LOB I/O unit size
RETENTION Controls undo retention vs versioning
CACHE/NOCACHE Controls buffer caching
ENABLE STORAGE IN ROW Stores LOB inline if small enough
SECUREFILE Enables compression, dedup, encryption
TABLESPACE Defines LOB segment storage location
As their name indicates, LOBs are large. They take up a lot of storage space, so there are times when you want to be able to control the storage space to work with the size of the LOB storage.

LOB procedures

Earlier in this module, we mentioned the DBMS_LOB built-in PL/SQL package. A number of procedures in this package help you adjust the way that LOBs are stored, but four of them of them are most commonly used, as shown in the following table:

FunctionSimplified syntaxNotes
DBMS_LOB.COPY() 
DBMS_LOB.COPY(
dest_lob,
src_lob 
dest_offset, 
src_offset, 
amt);

where dest_lob is the LOB locator to which the data in src_lob is to be copied to, src_lob is the LOB locator of thesource LOB, dest_offset is the offset from the beginning of the destination LOB, src_offset is the offset from the beginningof the source LOB, and amt is the amount of bytes to be copied.		You can also specify an offset to begin copying the data from src_lob, the number of bytes to be copied, and the offset for thecopied data to be placed at in dest_lob.
DBMS_LOB.APPEND() 
DBMS_LOB.APPEND(
dest_lob,
src_lob);
where dest_lob is the LOB locator that the data in the src_lob is to be appended to and src_lob is the LOB locator or the source LOB.		 This procedure will take the contents from one LOB and append them to the end of another LOB.
DBMS_LOB.TRIM() 
DBMS_LOB.TRIM(
lob_locator,
newlen);

where lob_locator is the LOB locator for the LOB and newlen is an integer representing the new number of bytes in the LOB.		You must first retrieve the locator for the LOB. newlen cannot be more than the existing length of the LOB or an exception will be returned.
DBMS_LOB.ERASE() 
DBMS_LOB.ERASE(
lob_loc,
amount, 
offset);

where lob_loc is the LOB locator for the target LOB, amount is an integer representing the number of bytes to be erased, andoffset is an integer representing the offset of the action.		 You can erase data from within a LOB by setting an offset and a number of characters. The size of the LOB is not reduced when theERASE() procedure is used.
The following series of images illustrates the use of these procedures:
DBMS_LOB functions 1
The image illustrates the use of the Oracle built-in procedure `DBMS_LOB.COPY` to copy data from one LOB (Large Object) to another. Here's a breakdown and analysis of the elements shown:
🔹 Function Call in the Image 
DBMS_LOB.COPY(src_lob, dest_lob, 1, 1, 2000);

Parameters:
  • src_lob: The source LOB from which data is being copied.
  • dest_lob: The destination LOB to which data is being copied.
  • 1: The amount of data to copy (likely 1 unit or chunk here).
  • 1: The destination offset — start writing at position 1 in dest_lob.
  • 2000: The source offset — start reading from position 2000 in src_lob.

🔹 Visual Elements Explained
  • Two purple bars represent the LOBs:
    • Top Bar = src_lob
    • Bottom Bar = dest_lob
  • Labels "2 0 0 0 bytes" next to each LOB indicate the state of data (possibly a chunk identifier or byte offset).
  • A downward arrow between src_lob and dest_lob symbolizes the copy operation.
  • The bottom text confirms the logic.

You can embed this within a larger HTML document, or place it inside a `
` or `
` depending on your layout.
> "With the COPY() function, the contents of the src\_lob are copied to the dest\_lob."
🔹 Interpretation of the Byte Values
* Both `src_lob` and `dest_lob` show: 
  2
  0
  0
  0 bytes
This may represent the total byte size of the LOB or the number of bytes being handled during the operation. It implies that 2,000 bytes (from offset 2000) are involved.
✅ Key Takeaways
  • DBMS_LOB.COPY is a manual copy operation between two LOBs.
  • You control the source offset, destination offset, and number of bytes to copy.
  • It’s often used when dealing with partially filled LOBs, or when you want to append/overwrite specific chunks of LOB data.

1) With the COPY() function, the content of the src_lob are copied to the dest_lob

Analyze the following image and describe the elements with respect to DBMS_LOB.APPEND(src_lob, dest_lob) DBMS_LOB functions 2
The image illustrates the behavior of the Oracle PL/SQL procedure: 
DBMS_LOB.APPEND(src_lob, dest_lob);
Here is a breakdown and explanation of the image components:
🔷 Purpose of the Function
The function appends the contents of the `src_lob` (source LOB) to the end of the existing data in the `dest_lob` (destination LOB).
🖼️ Visual Elements
  1. LOB Bars
    • Top purple bar labeled src_lob: Represents the source LOB that contains the data to be appended.
    • Bottom bar labeled dest_lob: Consists of two segments:
      • Green segment (left): Represents the existing content in dest_lob.
      • Purple segment (right): Represents the appended content copied from src_lob.
  2. Byte Labels
    • Next to both src_lob and dest_lob, the text: 
      2
0
0
0 bytes
      Likely indicates the internal chunk structure or byte boundaries of the LOB data.
  3. Arrow
    • A diagonal arrow points from src_lob to the end of dest_lob, clearly illustrating that the data is added after existing data, not overwritten.
  4. Caption
    • "With the APPEND() function, the contents of the src_lob are appended to the end of the contents of the dest_lob."
      Confirms the operational logic and the read/write behavior of the function.

🧠 Conceptual Summary
  • DBMS_LOB.APPEND does not erase or replace the data in dest_lob.
    • dest_lob := dest_lob || src_lob
  • Efficient for building LOBs incrementally, such as assembling a CLOB for a report or logging large text bodies.
2) With the APPEND() function, the contents of the src_lob are appended to the end of the contents of dest_lob

DBMS_LOB functions 3
The image explains the use of the Oracle PL/SQL procedure: 
DBMS_LOB.TRIM(src_lob, 1000);

🔍 Purpose of `DBMS_LOB.TRIM`
  • This function truncates the content of a LOB (Large Object) to the specified number of bytes.
  • In this case, the src_lob is reduced to 1000 bytes in length.

🖼️ Visual Elements Explained
  1. Top Bar – src_lob (Before TRIM)
    • A full-length purple bar representing the original src_lob.
    • Labeled with: 
      2
0
0
0 bytes
      This likely indicates that the LOB originally consists of 2 chunks (or 2,000 bytes).
  2. Arrow and Bottom Bar – src_lob (After TRIM)
    • A downward arrow points from the original src_lob to a smaller bar below, indicating transformation.
    • The smaller purple bar represents the trimmed version of the LOB.
    • Labeled: 
      1
0
0
0 bytes

      Suggesting the LOB has been shortened to approximately 1,000 bytes (or 1 chunk).
  3. Caption
    • "With the TRIM() function, the length of the data in the src_lob is reduced to the number of bytes specified in the function."
      This confirms the operation, only the first 1000 bytes of the LOB are retained.

✅ Summary of Behavior
Feature Description
Function DBMS_LOB.TRIM(src_lob, 1000)
Result LOB content is truncated to 1000 bytes
Affected LOB Only the source LOB (src_lob) is modified
Use Cases Discarding extra data, enforcing max sizes, etc.

3) With the TRIM() function, the length of the data in the src_lob is reduced to the number of bytes specified in the function

DBMS_LOB functions 4
4) With the ERASE() function, the data is erased from the src_lob, but the space is not reclaimed


In the next lesson, you will learn how Oracle uses LOBs to store large arrays.
SEMrush Software 2 SEMrush Banner 2