LOB in Oracle

Q: What is LOB (Large Objects)?

Ans:

We can use a LONG data type to store character data up to 2GB in length per row; the LONG RAW data type provides storage for long binary data. In place of LONG and LONG RAW we can use LOB data types (BLOB, CLOB, NCLOB, and BFILE) for storage of long data upto 4GB in length

BLOB  : Binary LOB; binary data, up to 4GB in length stored in the database

CLOB  : Character LOB; Character data up to 4GB in length, stored in Database

BFILE  : Binary File; read only binary data stored outside database, the length of which is limited by operating system.

NCLOB          : (National character LOB) A CLOB column that supports a multibyte character set.

Q: Will data integrity and concurrency are maintained for BFILE data types?

Ans:

Since the column uses BFILE datatype, its data is stored outside the database, the database stores only a locator value that allows it to find the external file. Oracle does not guarantee the data integrity of BFILE files stored outside the database. Oracle does not validate that the file exists when we insert a record using a BFILE datatype.Data concurrency and integrity are maintained only for internally stored LOBs.

Q: Where the LOB data is physically stored?

Ans:

The data for the LOB columns, whether stored inside or outside the database, may not be physically stored within the table. Within the table, Oracle stores locator values that point to data location. For BFILE datatypes, the locator points to an external file; for BLOB and CLOB datatypes, the locator points to a separate data location that the database creates to hold the LOB data. Except BFILE if the data less than 4kb then it will be stored at table.

Q: Advantage of LOB over LONG and RAW datatypes.

Ans:

Long and Raw stores data upto 2GB where as LOB can stores upto 4GB.

We can have only one LONG column in a table but we can have more than one LOB column in a table