C H A P T E R  11

Large Objects

Organizations often deal with substantial files that need to be stored and viewed by business users. Large objects (LOBs) generally refer to a data type that is suited for storing large and unstructured data such as text, log, image, video, sound, and spatial data. Oracle supports the following types of LOBs:

• LONG and LONG RAW
• Character large object (CLOB)
• National character large object (NCLOB)
• Binary large object (BLOB)
• Binary file (BFILE)

Prior to Oracle 8, the LONG and LONG RAW data types were your only options for storing large amounts of data in a column. You should no longer use these data types. The only reason I mention LONG and LONG RAW is because many legacy applications (Oracle's data dictionary, for example) still use them, and therefore you should be aware of their existence. You should otherwise use a CLOB or an NCLOB instead of LONG, and a BLOB instead of LONG RAW.

Describing Current LOB Types

Starting with Oracle version 8, the ability to store large files in the database vastly improved with the CLOB, NCLOB, BLOB, and BFILE data types. These additional LOB data types let you store much more data with greater functionality. Table 11–1 describes the types of Oracle LOBs available and their descriptions.

A CLOB is capable of storing large amounts of character data such as XML, text, or log files. An NCLOB is treated the same as a CLOB but can contain characters in the multibyte national character set for a database.

BLOBs store large amounts of binary data that typically isn't human readable. Typical uses for a BLOB are images, audio, and video data.

CLOBs, NCLOBs, and BLOBs are referred to as internal LOBs. This is because these data types are stored inside the Oracle database in datafiles. Internal LOBs participate in transactions and are covered by Oracle's database security, as well as its backup and recovery features.

BFILEs are referred to as external LOBs. BFILE columns store a pointer to a file on the operating system that is outside of the database. When it's not feasible to store a large binary file in the database, then use a BFILE.

Sometimes the question arises, “Should you use a BLOB or a BFILE?” BLOBs participate in database transactions and can be backed up, restored, and recovered by Oracle. BFILEs don't participate in database transactions, are read-only, and aren't covered by any Oracle security, backup and recovery, replication, or disaster-recovery mechanisms. BFILEs are more appropriate for large binary files that are read-only and don't change while an application is running. For example, you may have large binary video files that are referenced by a database application. In this scenario, the business determines that you don't need to create and maintain a 500TB database when all the application really needs is a pointer (stored in the database) to the locations of the large files on disk.

Table 11–1. Oracle Large Object Data Types

Data Type	Description	Maximum Size
LONG	Don't create tables with LONG data types This data type is supported for backward compatibility. Use a CLOB or an NCLOB instead.	2GB
LONG RAW	Don't create tables with LONG RAW columns. This data type is supported for backward compatibility. Use a BLOB instead.	2GB
CLOB	Character large object for storing character documents such as big text files, log files, XML files, and so on.	(4GB – 1)* blocksize
NCLOB	National character large object. Stores data in national character set format. Supports characters with varying width.	(4GB – 1) * blocksize
BLOB	Binary large object for storing unstructured bitstream data (images, video, and so on).	(4GB – 1) * blocksize
BFILE	Binary file large object stored on the filesystem outside of the database. BFILEs are read-only.	2^64 – 1 bytes (operating system may impose a size limit that is less than this)

Illustrating LOB Locators, Indexes, and Chunks

Internal LOBs (CLOB, NCLOB, and BLOB) store data in pieces called chunks. A chunk is the smallest unit of allocation for a LOB and is made up of one or more database blocks. A LOB locator is stored in a row that contains a LOB column. The LOB locator points to a LOB index. The LOB index stores information regarding the LOB chunks. When a table is queried, the database uses the LOB locator and associated LOB index to locate the appropriate LOB chunks. Figure 11–1 shows the relationship between a table, a row, a LOB locator, and its associated index and chunks.

Figure 11–1. Relationship of table, row, LOB locator, LOB index, and LOB segment

The LOB locator for a BFILE stores the directory path and file name on the operating system. Figure 11–2 shows a BFILE LOB locator that references a file on the operating system.

Figure 11–2. The BFILE LOB locator contains information to locate a file on the operating system.

Distinguishing Between BasicFiles and SecureFiles

Several significant improvements were made to LOBs in Oracle Database 11g. Oracle now distinguishes between two different types of underlying LOB architectures:

• BasicFile
• SecureFile

SecureFile is a new LOB architecture introduced in Oracle Database 11g. The SecureFile architecture has many new enhancements that improve the manageability and performance of LOBs. If you're using Oracle Database 11g or higher, then you should create your LOB columns with the SECUREFILE clause. Be aware that the SecureFile feature itself doesn't require an additional license. However, some of the SecureFile advanced features do require additional licenses (encryption, deduplication, and compression).

If you're not using Oracle Database 11g, then your only option is to use the BasicFile architecture. This is the default type of LOB created, and it's been available since Oracle version 8.

BasicFile

BasicFile is the name Oracle gives to the LOB architecture available prior to Oracle Database 11g. It's still important to understand the BasicFile LOBs because many shops use Oracle versions that don't support SecureFiles. You don't need to do anything special to enable the use of BasicFile LOBs; this is the default LOB architecture that is implemented when you create a table with LOB columns.

SecureFile

If you're using Oracle Database 11g or higher, then you have the option of using the SecureFile LOB architecture. It includes the following enhancements (over BasicFile LOBs):

• Encryption (requires Oracle Advanced Security Option)
• Compression (requires Oracle Advanced Compression Option)
• Deduplication (requires Oracle Advanced Compression Option)

SecureFile encryption lets you transparently encrypt LOB data (just like other data types). The compression feature allows for significant space savings. The deduplication feature eliminates duplicate LOBs that otherwise would be stored multiple times.

Prerequisites for SecureFiles

You need to do a small amount of planning before using SecureFiles. Specifically, use of SecureFiles requires the following:

• A SecureFile LOB must be stored in a tablespace using the automated segment space management feature (ASSM).
• The DB_SECUREFILE initialization setting must be either PERMITTED or ALWAYS.

A SecureFile LOB must be created within a tablespace using ASSM. To create an ASSM-enabled tablespace, specify the SEGMENT SPACE MANAGEMENT AUTO clause. For example:

If you have existing tablespaces, you can verify the use of ASSM by querying the DBA_TABLESPACES view. The SEGMENT_SPACE_MANAGEMENT column should have a value of AUTO for any tablespaces that you want to use with SecureFiles:

Here's a snippet of the output indicating that the USER1 tablespace is using ASSM:

Also, SecureFiles require that the DB_SECUREFILE initialization setting is either PERMITTED or ALWAYS. The default value is PERMITTED. You can verify the value as follows:

You can use either ALTER SYSTEM or ALTER SESSION to modify the value of DB_SECUREFILE. Table 11–2 describes the valid values for DB_SECUREFILE.

Table 11–2. Description of DB_SECUREFILE Settings

DB_SECUREFILE Setting	Description
NEVER	The LOB is created as a BasicFile regardless of whether the SECUREFILE option is specified.
PERMITTED	SecureFile LOBs can be created.
ALWAYS	The LOB is created as a SecureFile type unless the underlying tablespace isn't using ASSM.
IGNORE	The SECUREFILE option is ignored, along with any SecureFile settings.

Creating a Table with a LOB Column

By default, when you create a LOB, it's a BasicFile LOB. The next subsection covers BasicFiles and is followed by a subsection on creating a SecureFile LOB. If you're using Oracle Database 11g and higher, I recommend that you always create a LOB as a SecureFile LOB. As discussed previously, SecureFiles allow you to use features such as compression and encryption.

Creating a BasicFile LOB Column

To create a LOB column, you have to specify a LOB data type. Listed next is a basic example of creating a table with a CLOB data type:

When you create a table with a LOB column, you must be aware of some technical underpinnings. Review the following bulleted list and be sure you understand each point:

• LOBs by default are created as BasicFiles.
• Oracle creates a LOB segment and a LOB index for each LOB column.
• The LOB segment has a name of this format: SYS_LOB<string>.
• The LOB index has a name of this format: SYS_IL<string>.
• The <string> is the same for each LOB segment and its associated index.
• The LOB segment and index are created in the same tablespace as the table unless you specify a different tablespace.
• By default, nearly 4000 bytes of a LOB are stored in the table row (inline).
• With Oracle Database 11g release 2 and higher, a LOB segment and a LOB index aren't created until a record is inserted into the table (the so-called deferred segment creation feature). This means DBA/ALL/USER_SEGMENTS and DBA/ALL/USER_EXTENTS have no information in them until a row is inserted into the table.

Oracle creates a LOB segment and a LOB index for each LOB column. The LOB segment stores the data. The LOB index keeps track of where the chunks of LOB data are physically stored and in what order the LOB chunks should be accessed.

You can query the DBA/ALL/USER_LOBS view to display the LOB segment and LOB index names:

Here's the output for this example:

You can also query DBA/USER/ALL_SEGMENTS to view information regarding LOB segments. As mentioned earlier, if you create a table in Oracle Database 11g release 2 and higher, an initial segment isn't created until you insert a row into the table (deferred segment creation). This can be confusing because you may expect a row to be present in DBA/ALL/USER_SEGMENTS immediately after you create the table:

The prior query prompts for the segment names. The output shows no rows:

Next, insert a record into the table that contains the LOB column:

Rerunning the query against USER_SEGMENTS shows that three segments have been created—one for the table, one for the LOB segment, and one for the LOB index:

Creating a LOB in a Specific Tablespace

By default, the LOB segment is stored in the same tablespace as its table. You can specify a separate tablespace for a LOB segment by using the LOB...STORE AS clause of the CREATE TABLE statement. The next table-creation script creates the table in a tablespace, and creates separate tablespaces for the CLOB and BLOB columns:

You need to modify this query so that the tablespace names match your environment (or you can explicitly create CLOB_DATA and BLOB_DATA tablespaces). The following query verifies that three tablespaces are utilized for this table:

Here's the output:

I recommend that you always create a LOB with its storage specified in a separate tablespace from the table data. This is because LOBs have different growth patterns and require different storage characteristics (than the table data).

Creating a SecureFile LOB Column

If you don't specify the SECUREFILE clause when creating a table with a LOB column, then by default the LOB is created as a BasicFile LOB. This next example shows how to create a SecureFile LOB and place it in a tablespace separate from the table. As mentioned earlier, the tablespace that contains the Securefile LOB must be an ASSM-managed tablespace:

Before viewing the data dictionary, you can insert a record into the table to ensure that segment information is available (due to the deferred-segment-allocation feature in Oracle Database 11g release 2 and higher). For example:

You can now verify a LOB's architecture by querying the USER_SEGMENTS view:

Here's some sample output indicating that a LOB segment is a SecureFile:

You can also query the USER_LOBS view to verify the SecureFile LOB architecture:

Here's the output:

Creating a Partitioned LOB

You can create a partitioned table that has a LOB column. Doing so lets you spread a LOB across multiple tablespaces. Such partitioning helps with balancing I/O, maintenance, and backup and recovery operations.

You can partition LOBs by RANGE, LIST, or HASH. The next example creates a LIST-partitioned table in which LOB column data is stored in tablespaces separate from the table data:

Notice that each LOB partition is created with its own storage options (the SecureFile features are covered a bit later in this chapter). You can view the details about the LOB partitions as shown:

Here's some sample output:

You can change the storage characteristics of a partitioned LOB column after it's been created. To do so, use the ALTER TABLE ... MODIFY PARTITION statement. This example alters a LOB partition to have a high degree of compression:

The next example modifies a partitioned LOB so it doesn't keep duplicate values (via the DEDUPLICATE clause):

Maintaining LOB Columns

The following sections describe some common maintenance tasks that are performed on LOB columns or that involve LOB columns. You learn to move columns between tablespaces, to add new LOB columns to a table, and so forth.

Moving a LOB Column

As mentioned previously, if you create a table with a LOB column and don't specify a tablespace, then by default the LOB is created in the same tablespace as its table. This happens sometimes in environments where the DBAs don't plan ahead very well; only after the LOB column has consumed large amounts of disk space does the DBA wonder why the table has grown so big.

You can use the ALTER TABLE...MOVE...STORE AS statement to move a LOB column to a separate tablespace (from the table's tablespace). Here's the basic syntax:

The next example moves the LOB column to the INV_CLOB tablespace:

You can verify that the LOB was moved by querying USER_LOBS:

If the LOB column is populated with large amounts of data, you almost always want to store the LOB in a tablespace separate from the rest of the table data. In these scenarios, the LOB data has different growth and storage requirements and is best maintained in its own tablespace.

Adding a LOB Column

If you have an existing table to which you want to add a LOB column, use the ALTER TABLE...ADD statement. The next statement adds the INV_IMAGE column to a table:

This statement is fine for quickly adding a LOB column to a development environment. For anything else, you should specify the storage characteristics. For example, this specifies that a SecureFile LOB is created in the LOB_DATA tablespace:

Removing a LOB Column

You may have a scenario where your business requirements change and you no longer need a column. Before you remove a column, consider renaming it so that you can better identify whether any applications or users are still accessing it:

After you determine that nobody is using the column, use the ALTER TABLE...DROP statement to drop it:

You can also remove a LOB column by dropping and re-creating a table (without the LOB column). This of course permanently removes any data as well.

Also keep in mind that in Oracle Database 10g or higher, if your recycle bin is enabled, then when you don't drop a table with the PURGE clause, space is still consumed by the dropped table. If you want to remove the space associated with the table, use the PURGE clause or purge the recycle bin after dropping the table.

Caching LOBs

By default, when reading and writing LOB columns, Oracle doesn't cache LOBs in memory. You can change the default behavior by setting the cache-related storage options. This example specifies that Oracle should cache a LOB column in memory:

You can verify the LOB caching with this query:

Here's some sample output:

Table 11–3 describes the memory cache settings related to LOBs. If you have LOBs that are frequently read and written to, consider using the CACHE option. If your LOB column is read frequently but rarely written to, then the CACHE READS setting is more appropriate. If the LOB column is infrequently read or written to, then the NOCACHE setting is suitable.

Table 11–3. Cache Descriptions Regarding LOB Columns

Cache Setting	Meaning
CACHE	Oracle should place LOB data in the buffer cache for faster access.
CACHE READS	Oracle should place LOB data in the buffer cache for reads but not for writes.
NOCACHE	LOB data shouldn't be placed in the buffer cache. This is the default for both SecureFile and BasicFile LOBs.

Storing LOBs In and Out of Line

By default, up to approximately 4000 characters of a LOB column are stored in line with the table row. If the LOB is over 4000 characters, then Oracle automatically stores the LOB outside of the row data. The main advantage of storing a LOB in row is that small LOBs (less than 4000 characters) require less I/O, because Oracle doesn't have to search out of row for the LOB data.

However, storing LOB data in row isn't always desirable. The disadvantage of storing LOBs in row is that the table row sizes are potentially longer. This can impact the performance of full-table scans, range scans, and updates to columns other than the LOB column. In these situations, you may want to disable storage in the row. For example, you explicitly instruct Oracle to store the LOB outside of the row with the DISABLE STORAGE IN ROW clause:

If you want to store up to 4000 characters of a LOB in the table row, use the ENABLE STORAGE IN ROW clause when creating the table:

You can't modify the LOB storage in a row after the table has been created. The only ways to alter storage in row is to either move the LOB column or drop and re-create the table. This example alters the storage in row by moving the LOB column:

You can verify the in-row storage via the IN_ROW column of USER_LOBS:

A value of YES indicates that the LOB is stored in row:

Using SecureFile Features

As mentioned previously in this chapter, the SecureFile LOB architecture allows you to compress LOB columns, eliminate duplicates, and transparently encrypt LOB data. These features provide high performance and manageability of LOB data and are available in Oracle Database 11g and higher. The next few subsections cover features specific to SecureFiles.

Compressing LOBs

If you're using SecureFile LOBs, then you can specify a degree of compression. The benefit is that the LOBs consume much less space in the database. The downside is that reading and writing the LOBs may take longer. See Table 11–4 for a description of the compression values.

This example creates a CLOB column with a low degree of compression:

Table 11–4. Degrees of Compression Available with SecureFile LOBs

Compression Type	Description
HIGH	Highest degree of compression. Incurs higher latency when reading and writing the LOB.
MEDIUM	Medium level of compression. Default value if compression is specified but with no degree.
LOW	Lowest level of compression. Provides the lowest latency when reading and writing the LOB.
COMPRESS	clause isn't specified. No compression is used if you don't specify the COMPRESS clause.

If a LOB has been created as a SecureFile, you can alter its compression level. For example, this changes the compression to HIGH:

If you create a LOB with compression but decide that you don't want to use the feature, you can alter the LOB to have no compression via the NOCOMPRESS clause:

Deduplicating LOBs

If you have an application where identical LOBs are associated with two or more rows, you should consider using the SecureFile deduplication feature. When enabled, this instructs Oracle to check when a new LOB is inserted into a table and see whether that LOB is already stored in another row (for the same LOB column). If it's already stored, then Oracle stores a pointer to the existing identical LOB. This can potentially mean huge space savings for your application.

This example creates a LOB column using the deduplication feature:

To verify that the deduplication feature is in effect, run this query:

Here's some sample output:

If an existing table has a SecureFile LOB, then you can alter the column to enable deduplication:

Here's another example that modifies a partitioned LOB to enable deduplication:

If you decide that you don't want deduplication enabled, use the KEEP_DUPLICATES clause:

Encrypting LOBs

You can transparently encrypt a SecureFile LOB column (just like any other column). Before you use encryption features, you must set up an encryption wallet. If you don't know how to setup a wallet, I've included a sidebar at the end of this subsection that describes this task. Also, see the Oracle Advanced Security guide (available on OTN) for more details.

The ENCRYPT clause enables SecureFile encryption using Oracle Transparent Data Encryption (TDE). The following example enables encryption for the PATCH_DESC LOB column:

When you describe the table, the LOB column now shows that encryption is in effect:

Here's a slightly different example that specifies the ENCRYPT keyword in line with the LOB column:

You can verify the encryption details by querying the DBA_ENCRYPTED_COLUMNS view:

Here's the output for this example:

If you've already created the table, you can alter a column to enable encryption:

You can also specify an encryption algorithm. For example:

You can disable encryption for a SecureFile LOB column via the DECRYPT clause:

CREATING AN ENCRYPTION WALLET

Migrating BasicFiles to SecureFiles

You can migrate BasicFile LOB data to SecureFiles via one of the following methods:

• Create a new table, load the data from the old table, and rename the tables.
• Move the table.
• Redefine the table online.

Each of these techniques is described in the following subsections.

Creating a New Table

Here's a brief example of creating a new table and loading data from the old table. In this example, PATCHMAIN_NEW is the new table being created with a SecureFile LOB.

Next, load the newly created table with data from the old table:

Now, rename the tables:

When using this technique, be sure any grants that were pointing at the old table are reissued for the new table.

Moving a Table to a SecureFile Architecture

You can also use the ALTER TABLE...MOVE statement to redefine the storage of a LOB as a SecureFile. For example:

You can verify that the column is now a SecureFile via this query:

The SECUREFILE column now has a value of YES:

Online Redefinition

You can also redefine a table while it's online via the DBMS_REDEFINITION package. Use the following steps to do an online redefinition:

1. Ensure that the table has a primary key. If the table doesn't have a primary key, then create one:
   alter table patchmain
add constraint patchmain_pk
primary key (patch_id);
2. Create a new table that defines the LOB column(s) as SecureFile:
   create table patchmain_new(
 patch_id number
,patch_desc clob)
lob(patch_desc)
store as securefile (tablespace lob_data);
3. Map the columns of the new table to the original table:
   declare
  l_col_map varchar2(2000);
begin
  l_col_map := 'patch_id patch_id, patch_desc patch_desc';
  dbms_redefinition.start_redef_table(
   'DARL','PATCHMAIN','PATCHMAIN_NEW',l_col_map
  );
end;
/
4. Copy the data (this can take a long time if there are many rows):
   set serverout on size 1000000
declare
  l_err_cnt integer :=0;
begin
  dbms_redefinition.copy_table_dependents(
   'DARL','PATCHMAIN','PATCHMAIN_NEW',1,TRUE, TRUE, TRUE, FALSE, l_err_cnt
  );
  dbms_output.put_line('Num Errors: ' || l_err_cnt);
end;
/
5. Finish the redefinition:
   begin
  dbms_redefinition.finish_redef_table('DARL','PATCHMAIN','PATCHMAIN_NEW'),
end;
/

You can confirm that the table has been redefined via this query:

Here's the output for this example:

Viewing LOB Metadata

You can use any of the DBA/ALL/USER_LOBS views to display information about LOBs in your database:

Table 11–5 describes the columns available with DBA/ALL_LOBS:

Table 11–5. ALL_LOBS Column Descriptions

Column	Description
OWNER	Owner of the object that contains the LOB.
TABLE_NAME	Table name that contains the LOB.
COLUMN_NAME	Column name of the LOB.
SEGMENT_NAME	Segment name of the LOB.
TABLESPACE_NAME	Tablespace name that contains the LOB.
INDEX_NAME	Index name of the LOB.
CHUNK	Size in bytes of the LOB chunk.
PCTVERSION	Maximum percentage of LOB space used for versioning.
RETENTION	Maximum time duration for versioning of LOB space.
FREEPOOLS	Number of free pools for the LOB.
CACHE	YES indicates that the LOB data is placed in the buffer cache. NO indicates that the LOB data isn't placed in the buffer cache. CACHEREADS indicates that the LOB is brought into the cache for read operations only.
LOGGING	Whether changes to the LOB are logged in the redo stream.
ENCRYPT	Whether the LOB is encrypted.
COMPRESSION	Degree of compression,
DEDUPLICATION	Whether deduplication is used for the LOB.
IN_ROW	Whether some of the LOB is stored in line with the row.
FORMAT	Whether the LOB storage format depends on the endianness of the platform.
PARTITIONED	Whether the LOB is in a partitioned table.
SECUREFILE	Whether the LOB architecture is SecureFile.
SEGMENT_CREATED	Whether the LOB segment has been created.

Also keep in mind that a LOB segment has a corresponding index segment. Thus you can query both the segment and the index in the DBA/ALL/USER_SEGMENTS views for LOB information:

Loading LOBs

Loading LOB data isn't typically the DBA's job, but you should be familiar with techniques used to populate LOB columns. Developers may come to you for help with troubleshooting, performance, or space-related issues.

Loading a CLOB

First, create an Oracle database directory object that points to the operating system directory in which the CLOB file is stored. This directory object is used when loading the CLOB. In this example, the Oracle directory object is named LOAD_LOB and the operating system directory is /home/oracle/scripts:

For reference, listed next is the DDL used to create the table in which the CLOB file is loaded:

This example also uses a sequence named PATCH_SEQ. Here's the sequence-creation script:

The following bit of code uses the DBMS_LOB package to load a text file (named patch.txt) into a CLOB column. In this example, the table name is PATCHMAIN and the CLOB column is PATCH_DESC:

You can place this code in a file and execute it from the SQL command prompt. In this example, the file that contains the code is named clob.sql:

Here's the expected output:

Loading a BLOB

Loading a BLOB is similar to loading a CLOB. This example uses the same directory object, table, and sequence from the previous example, which loaded a CLOB. Loading a BLOB is simpler than loading a CLOB because you don't have to specify character-set information.

This example loads a file named patch.zip into the PATCH_FILE BLOB column:

You can place this code in a file and run it from the SQL command prompt. In this example, the file that contains the code is named blob.sql:

Here's the expected output:

Measuring LOB Space Consumed

As mentioned previously in this chapter, a LOB consists of an in-row lob locator, a LOB index, and a LOB segment that consists of one or more chunks. The space used by the LOB index is usually negligible compared to the space used by the LOB segment. You can view the space consumed by a segment by querying the BYTES column of DBA/ALL/USER_SEGMENTS (just like any other segment in the database). Here's a sample query:

You can modify the query to specifically report on only LOBs by joining to the USER_LOBS view:

You can use the DBMS_SPACE.SPACE_USAGE package and procedure to report on the blocks being used by a LOB. This package only works on objects that have been created in an ASSM-managed tablespace. There are two different forms of the SPACE_USAGE procedure: one form reports on BasicFile LOBs, and the other form reports on SecureFile LOBs.

BasicFile Space Used

Here's an example of how to call DBMS_SPACE.SPACE_USAGE for a BasicFile LOB:

In this PL/SQL, you need to modify the code so that it reports on the LOB segment in your environment.

SecureFile Space Used

Here's an example of how to call DBMS_SPACE.SPACE_USAGE for a SecureFile LOB:

Again, in this PL/SQL, you need to modify the code so that it reports on the table with the LOB segment in your environment.

Summary

Oracle lets you store large objects in databases via various LOB data types. LOBs facilitate the storage, management, and retrieval of video clips, images, movies, word-processing documents, large text files, and so on. Oracle can store these files in the database and thus provide backup and recovery and security protection (just as it does for any other data type). Oracle's CLOB data type is used to store large character text files that exceed 4000 characters (the maximum length of a VARCHAR2 column). BLOBs are used to store binary files such as images (JPEG or MPEG), movie files, sound files, and so on. If it's not feasible to store the file in the database, you can use a BFILE LOB.

Oracle provides two underlying architectures for LOBS: BasicFile and SecureFile. BasicFile is the LOB architecture that has been available since Oracle version 8. The SecureFile feature was introduced in Oracle Database 11g. SecureFile has many advanced options such as compression, deduplication, and encryption (these specific features require an extra license from Oracle).

LOBs provide a way to manage very large files. Oracle has another feature, partitioning, which allows you to manage very large tables and indexes. Partitioning is covered in detail in the next chapter.