René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -

LOB datatypes in Oracle


Large objects (also called LOBs) come in two flavors: internal lobs and external lobs.

Internal lobs

There are three types of internal lobs:
  • blob:
    Binary LOB
  • clob:
    Character LOB (single byte)
  • nclob:
    Character LOB (multi byte)
Internal LOBS are subject to transactions, that is, their value can be rolled back.

Lobsegment and lobindex

Each (internal) lob column within a table has an associated lobindex and lobsegment. The name of these can be viewed through xxx_lobs.

In row and out of row lobs

A lob can either be stored along the row to which it belongs (called in row storage) or in the lobsegment (called out of row storage). The maximum size for in row lobs is 3964 bytes.

Chunk size

A lob's chunk size determines the granularty of allocation for out of row lobs.

External lobs

An external lob is stored in a file within the filesystem (not in a tablespace) on which the Oracle server runs. They are also called BFILEs.
A BFILE is not subject to transactions.


Every LOB (that is, the lob's value) has a locator associated with it. This locator is used to actually find the LOB's value. A select statement that contains a LOB in the select list returns the locator rather than the lob's value.
A locator is initializes with either empty_blob() or empty_clob.
In order to manipulate the lob's value, dbms_lob is used.