Lesson 9

LOBs are designed to handle some specific data storage and processing scenarios. In a strict OLAP environment, there may not be a lot of call for using LOBs, but storing large objects in your Oracle database can help in creating other types of interactive applications.
In this module, you learned how to:

1. Describe the different types of LOB data types
2. Differentiate between the criteria for selecting one storage format over another for LOB data
3. Move data from LONG and LONG RAW columns to a LOB column
4. Use LOB commands to adjust the storage size of LOBs
5. Describe how Oracle uses LOBs to store large varrays
6. Understand how and why to use temporary LOBs

Discern when to buffer LOBs With these enhancements in Oracle, you will be able to extend the use of your Oracle database to manage a much wider variety of data.

LOB data (in BLOB or CLOB datatypes) is usually stored apart from the main table. You can use the LOB clause of the CREATE TABLE command to specify the storage attributes for the LOB data, such as a different tablespace. In the main table, Oracle stores a LOB locator value that points to the LOB data. When the LOB data is stored out of line, between 36 and 86 bytes of control data (the LOB locator) remain inline in the row piece. Oracle does not always store the LOB data apart from the main table. In general, the LOB data is not stored apart from the main table until the LOB data and the LOB locator value total more than 4000 bytes. Therefore, if you will be storing short LOB values, you need to consider their impact on the storage of your main table. If your LOB values are less than 32,768 characters, you may be able to use VARCHAR2 datatypes instead of LOB datatypes in Oracle Database 12c for the data storage, but those VARCHAR2 columns will still be stored out of line as a SecureFile LOB.

To explicitly specify where the LOB will reside if its size is 4000 bytes or less, use the DISABLE STORAGE IN ROW or ENABLE STORAGE IN ROW clause in the LOB storage clause of the CREATE TABLE statement. If a LOB is stored inline, and its value starts out with a size less than 4000 bytes, it will migrate to out of line. If an out-of-line LOB’s size becomes less than 4000 bytes, it stays out of line. The next module will discuss key features of Oracle LOB storage.