Subscribe YouTube Channel For More Live Tutorials

Large object (LOB) data types enable you to store and manipulate large blocks of unstructured data in binary or character format.

The large object (LOB) data type provides efficient, random, piece-wise access to the data.

Internal LOBs

An internal LOB stores data in the database itself rather than in external files.

Internal LOBS include the following:

  • CLOB (character LOB), which stores large amounts of text, such as text or XML files, in the database character set
  • NCLOB (national character set LOB), which stores Unicode data
  • BLOB (binary LOB), which stores large amounts of binary information as a bit stream and is not subject to character set translation

The database stores LOBs differently from other data types. Creating a LOB column implicitly creates a LOB segment and a LOB index. The tablespace containing the LOB segment and LOB index, which are always stored together, may be different from the tablespace containing the table.

The LOB segment stores data in pieces called chunks. A chunk is a logically contiguous set of data blocks and is the smallest unit of allocation for a LOB. A row in the table stores a pointer called a LOB locator, which points to the LOB index. When the table is queried, the database uses the LOB index to quickly locate the LOB chunks.

The database manages read consistency for LOB segments differently from other data. Instead of using undo data to record changes, the database stores the before images in the segment itself. When a transaction changes a LOB, the database allocates a new chunk and leaves the old data in place. If the transaction rolls back, then the database rolls back the changes to the index, which points to the old chunk.

External LOBs

A BFILE (binary file large object, or LOB) is an external large object.

A BFILE is an external LOB, because the database stores a pointer to a file in the operating system. The external data is read-only.

A BFILE uses a directory object to locate data. The amount of space consumed depends on the length of the directory object name and the length of the file name.

A BFILE does not use the same read consistency mechanism as internal LOBS because the binary file is external to the database. If the data in the file changes, then repeated reads from the same binary file may produce different results.

Secure Files

SecureFiles LOB storage is one of two storage types; the other type is BasicFiles LOB storage.

The SECUREFILE LOB parameter enables advanced features, including compression and deduplication (part of the Advanced Compression Option), and encryption (part of the Advanced Security Option). Starting with Oracle Database 12c, SecureFiles is the default storage mechanism for LOBs.