LOB datatypes in Oracle

LOBs support storing large, unstructured objects such as audio, video, picture, text, etc.

Before the LOB, there was LONG and LONG RAW datatypes which also were storing large objects. Oracle still supports it, but strongly recommends using LOBs instead of them. LONG and LONG raw have many restrictions and have less opportunity than LOBs. So let’s discuss their differences:

1. Table can store multiple LOBs, while you are restricted to use only one LONG column per table.
2. A LOB can store maximum 4GB in Oracle 8, 8TB in Oracle 9i/10g, 128TB in Oracle 11g. While LONG column can store maximum 2GB.
3. Table containing LOBs can be partitioned, while table with LONG column cannot be partitioned.
4. When you are using LOBs you are able to access its data randomly, while you must sequentially read LONG type data from beginning to end.
5. LOBs can be used in user defined data types (except NCLOB), while LONG cannot be used.

LOB datatypes can be stored inline (within a table) or out-of line (within a tablespace, using a LOB locator) or as an external file (BFILE).

BLOB

BLOBs are binary objects, used to store binary, raw data. BLOBs participate into transaction and can be rolled back or committed.

CLOB

CLOBs are character LOBs. Used to store single byte character set (large texts, xml…). They also participate into transactions.

NCLOB

NCLOBs are used to store multi-byte character data, which corresponds to the national character set defined into the database. They also are participating into transactions.

Three of them are types of Internal LOBs; there also exists external LOB called BFILE. It is the single type, which is external.

BFILE

BFILE is short for Binary File, which is stored outside the database. It is stored into the Operating System as a file. It doesn’t participate into transactions. The amount of file is also limited by OS. BFILEs are read only. They only support random reads, means not sequential read. The changes to the BFILEs can be done through OS.