Lesson 2	Review of LOB types
Objective	Describe the different types of LOB data types in Oracle 23ai

Oracle 23ai LOB Data Types

Large Objects (LOBs) are specialized Oracle data types engineered to store and manipulate massive text or binary payloads. While standard scalar types like VARCHAR2 or RAW are capped at 32 KB, LOBs can scale up to 128 terabytes depending on the database block size. In Oracle 23ai, LOB management is optimized through SecureFiles, the high-performance storage architecture that supports compression, deduplication, and encryption.

Categories of LOBs

LOBs are categorized based on where they are stored and the nature of the data they contain. Internal LOBs are stored within the database's tablespaces and benefit from full ACID compliance. External LOBs (BFILEs) reside in the operating system's file system, with the database maintaining only a pointer (locator).

LOB Datatype	Data Description	Modern Usage (Oracle 23ai)
`BLOB`	Unstructured binary data stored internally.	Images, video files, PDFs, and encrypted application binaries.
`CLOB`	Character data stored internally using the database character set.	Large text documents, XML files, and legacy text-based logs.
`NCLOB`	Character data using the National Character Set (Unicode).	Globalized applications requiring multilingual text storage.
`JSON`	Optimized binary-encoded JSON (OSON) stored as a LOB.	New Standard: High-performance storage for JSON documents in 23ai.
`BFILE`	Binary data stored in external OS files (Read-Only).	Archival content or large media libraries managed by external systems.

LOB Storage Architecture in 23ai

When working with Oracle 23ai, it is critical to distinguish between storage formats:

SecureFiles: The default and recommended architecture. It provides superior performance through features like DEDUPLICATE (saving space on redundant data) and COMPRESS.
BasicFiles: The legacy architecture. While still supported for backward compatibility, it lacks the advanced optimizations found in modern 23ai environments.

Strategic Implementation Guidelines

To maximize efficiency in your Oracle 23ai database, consider these selection criteria:

Consistency: Use internal LOBs (BLOB, CLOB) for any data that requires transactional integrity and database-level backups.
Efficiency: Use the DBMS_LOB package for piecewise operations to avoid loading massive objects into application memory entirely.
Searchability: Leverage Oracle Text or JSON indexes to query content within LOBs without performance degradation.


-- Example: Creating a table with modern LOB storage
CREATE TABLE project_repository (
project_id   NUMBER PRIMARY KEY,
project_name VARCHAR2(100),
doc_content  CLOB,
attachment   BLOB
) LOB (doc_content, attachment) STORE AS SECUREFILE (
COMPRESS MEDIUM
DEDUPLICATE
);

In the next lesson, we will explore LOB storage parameters and how to optimize tablespace placement for peak performance.