Managing Large Objects (LOBs) and Memory in Oracle
Large Objects (LOBs) are Oracle data types designed for storing and processing large text and binary payloads—documents, logs, JSON/XML, images, exports, application blobs, and other “too big for a VARCHAR2/RAW” content. In modern Oracle (including Oracle 23ai), the important part isn’t just that LOBs can store large values—it’s how LOB workloads interact with memory, redo/undo, and the TEMP tablespace.
This module introduces the LOB landscape and the performance decisions that come with it: choosing the right LOB type, understanding where LOB data is stored (in-row vs out-of-row), and using LOB APIs in ways that avoid unnecessary copying, memory pressure, and TEMP growth.
LOB types you will work with
Oracle provides multiple LOB families so you can match the storage and access pattern to the data:
| LOB Type | Data Type | Typical Use |
| BLOB | Binary | Binary payloads such as images, PDFs, compressed data, and application artifacts. |
| CLOB | Character | Large text such as documents, long-form content, JSON/XML text, and generated reports. |
| NCLOB | National Character | Large text stored using national character set semantics (use when NCHAR/NVARCHAR2 rules apply). |
| BFILE | External reference | A locator to a file outside the database (use when the database should reference, not own, the bytes). |
In practice, most application-owned LOB storage is handled as internal LOBs (BLOB/CLOB/NCLOB). Oracle stores internal LOB data either partially in-row (for small values) or out-of-row in LOB segments, and Oracle’s access path determines how much data is pulled into memory during reads and writes.
Why LOBs are a tuning topic (memory + TEMP)
LOB processing can become a performance and capacity concern when large payloads are created, copied, searched, or transformed. As a DBA or developer, you care about:
- Memory pressure: avoid “accidental full reads” of large LOBs into PGA/buffer cache when you only need a slice.
- Redo/undo volume: large LOB writes can generate significant change data depending on how the workload is structured.
- TEMP usage: temporary LOBs and some LOB operations consume TEMP; in pooled sessions, TEMP can grow silently if not managed.
- Storage behavior: SecureFiles features (compression, deduplication, encryption) can improve manageability but must be chosen deliberately.
Oracle’s modern LOB implementation (commonly SecureFiles) is designed for performance and manageability, including support for encryption and efficient storage features. But your schema choices and API usage still determine whether LOBs behave like a smooth streaming workload—or a memory/TEMP hotspot.
Module objectives
In this module, you will build practical skill with LOB data types and the decisions that affect their performance characteristics in Oracle 23ai.
- Describe the different types of LOB data types
- Differentiate between the criteria for selecting one storage format over another for LOB data
- Move data from LONG and LONG RAW columns to a LOB column
- Use LOB commands to adjust the storage size of LOBs
- Describe how Oracle uses LOBs to store large varrays
- Understand how and why to use temporary LOBs
- Discern when to buffer LOBs
Even if your day-to-day schemas rarely include LOB columns, many Oracle features and applications rely on LOB mechanics behind the scenes. Knowing how Oracle stores and processes LOBs helps you prevent avoidable performance issues and make better design choices as your data volumes grow.
Legacy columns: why LONG and LONG RAW should be migrated
If you still have LONG or LONG RAW in legacy schemas, treat them as technical debt. Modern Oracle features, tooling, and SQL functionality are far more robust with LOB types. Part of this module focuses on migrating legacy LONG/LONG RAW columns into LOB columns for better maintainability and feature support.
What’s next
In the next lesson, we’ll look more closely at LOB data types and how to choose the correct type and storage approach for the workload you are tuning.