|René Nyffenegger's collection of things on the web|
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
LOB datatypes in Oracle
Large objects (also called LOBs) come in two flavors: internal lobs and external lobs.
There are three types of internal lobs:
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.
A lob's chunk size determines the granularty of allocation for out of row 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
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
In order to manipulate the lob's value, dbms_lob is used.