Organizations often deal with substantial files that need to be stored and viewed by business users. Generally, LOBs are a data type that is suited to storing large and unstructured data, such as text, log, image, video, sound, and spatial data. Oracle supports the following types of LOBs:
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 (e.g., Oracle’s data dictionary) still use them. You should otherwise use a CLOB instead of LONG and a BLOB instead of LONG RAW.
Also, do not confuse a RAW data type with a LONG RAW. The RAW data type stores small amounts of binary data. The LONG RAW data type has been deprecated for more than a decade.
Another caveat: do not unnecessarily use a LOB data type. For example, for character data, if your application requires fewer than 32,000 single byte characters, use a VARCHAR2 data type CLOB). For binary data, if you are dealing with fewer than 32,000 bytes of binary data, use a RAW data type (and not a BLOB). If you are still not sure which data type your application needs, see Chapter 7 for a description of appropriate uses of Oracle data types.
Before lobbing you into the details of implementing LOBs, it is prudent to review each LOB data type and its appropriate use. After that, examples are provided of creating and working with LOBs and relevant features that you should understand.
Describing LOB Types
Since the earlier versions of Oracle, 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 summarizes the types of Oracle LOBs available and their descriptions.
Table 11-1
Oracle Large Object Data Types
Data Type
Description
Maximum Size
CLOB
Character large object for storing character documents, such as big text files, log files, XML files, and so on
(4GB–1)* block size
NCLOB
National character large object; stores data in national character set format; supports characters with varying widths
(4GB–1) * block size
BLOB
Binary large object for storing unstructured bitstream data (images, video, and so on)
(4GB–1) * block size
BFILE
Binary file large object stored on the filesystem outside of database; read-only
2^64–1 bytes (OS may impose a size limit that is less than this)
A CLOB such as json, XML, text, and log files. An NCLOB is treated the same as a CLOB but can contain characters in the multibyte national character set for a database.
BLOBsare not human readable. Typical uses for a BLOB are spreadsheets, word-processing documents, images, and audio and video data.
CLOBs, NCLOBs, and BLOBs are known as internal LOBs. This is because these data types are stored inside the Oracle database in data files. Internal LOBs participate in transactions and are covered by Oracle’s database security as well as its backup and recovery features.
BFILEs are known as external LOBs. BFILE columns store a pointer to a file on the OS that is outside the database. You can think of a BFILE as a mechanism for providing read-only access to large binary files outside the database on the OS filesystem.
Sometimes, the question arises as to whether you should use a BLOB or a BFILE. BLOBs participate in database transactions and can be backed up, restored, and recovered by Oracle. BFILEs do not participate in database transactions, are read-only, and are not 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 that do not change while an application is running. For instance, you may have large binary video files that are referenced by a database application. In this scenario, the business determines that you do not 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.
Illustrating LOB Locators, Indexes, and Chunks
Internal LOBs (CLOB, NCLOB, 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. LOB locators are stored in rows containing a LOB column. The LOB locator points to a LOB index. The LOB index stores information regarding the location of 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 a LOB locator’s 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 OS. Figure 11-2 shows a BFILE LOB locator that references a file on the OS.
Figure 11-2
The BFILE LOB locator contains information for locating a file on the OS
Note
The DBMS_LOB package performs operations on LOBs through the LOB locator.
Distinguishing Between BasicFiles and SecureFiles
Several significant improvements were made to LOBs. Oracle now distinguishes between two different types of underlying LOB architecture:
BasicFiles
SecureFiles
These two LOB architectures are discussed in the following sections.
BasicFiles
BasicFiles is the name Oracle gives to the LOB architecture available prior to Oracle Database 11g. It is still important to understand the BasicFiles LOBs because many shops use Oracle versions that do not support SecureFiles. Be aware that in Oracle Database 11g, the default type of LOB is still BasicFiles. However, now, the default type of LOB is now SecureFiles and should be used as the way to store LOBs.
SecureFiles
SecureFiles is the recommended option to use with the LOB architecture. It includes the following enhancements (over BasicFiles LOBs):
SecureFiles 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.
You need to do a small amount of planning before using SecureFiles. Specifically, use of SecureFiles requires the following:
A SecureFiles LOB must be stored in a tablespace, using ASSM.
The DB_SECUREFILE initialization parameter controls whether a SecureFiles file can be used and also defines the default LOB architecture for your database.
A SecureFiles LOB must be created within a tablespace using ASSM. To create an ASSM-enabled tablespace, specify the SEGMENT SPACE MANAGEMENT AUTO clause; for example,
SQL> create tablespace lob_data
datafile '/u01/dbfile/o18c/lob_data01.dbf'
size 1000m
extent management local
uniform size 1m
segment space management auto;
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:
select tablespace_name, segment_space_management
from dba_tablespaces;
Also, SecureFiles usage is governed by the DB_SECUREFILE database parameter. 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_SECUREFILESetting
Description
NEVER
Creates the LOB as a BasicFiles type, regardless of whether the SECUREFILE option is specified
PERMITTED
Allows creation of SecureFiles LOBs
PREFERRED
Default value; specifies that all LOBs are created as a SecureFiles type, unless otherwise stated
ALWAYS
Creates the LOB as a SecureFiles type, unless the underlying tablespace is not using ASSM
IGNORE
Ignores the SecureFiles option, along with any SecureFiles settings
Creating a Table with a LOB Column
The default underlying LOB architecture is SecureFiles. It is recommened to create a LOB as a SecureFiles. As discussed previously, SecureFiles allows you to use features such as compression and encryption.
Creating a BasicFiles LOB Column
To create a LOB column, you have to specify a LOB data type. It is best to explicitly specify the STORE AS BASICFILE clause in order to avoid confusion as to which LOB architecture is implemented. Listed next is such an example:
SQL> create table patchmain(
patch_id number
,patch_desc clob)
tablespace users
lob(patch_desc) store as basicfile;
When you create a table with a LOB column, you must be aware of some technical underpinnings. Review the following list, and be sure you understand each point:
Prior to Oracle Database 12c, LOBs, by default, are created as the BasicFiles type.
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.
A LOB segment and a LOB index are not created until a record is inserted into the table (the so-called deferred segment creation feature). This means that 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 they should be accessed.
You can query the DBA/ALL/USER_LOBS view to display the LOB segment and LOB index names:
PATCHMAIN SYS_LOB0000022332C00002$$ SYS_IL0000022332C00002$$ NO YES
You can also query DBA/USER/ALL_SEGMENTS to view information regarding LOB segments. As mentioned earlier, an initial segment is not 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:
no rows selected
Next, insert a record into the table that contains the LOB column:
SQL> insert into patchmain values(1,'clob text');
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:
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:
SQL> create table patchmain
(patch_id number
,patch_desc clob
,patch blob
) tablespace users
lob (patch_desc) store as (tablespace lob_data)
,lob (patch) store as (tablespace lob_data);
The following query verifies the associated tablespaces for this table:
If you think the LOB segment will require different storage characteristics (such as size and growth patterns), then I recommend that you create the LOB in a tablespace separate from that of the table data. This allows you to manage the LOB column storage separately from the regular table data storage.
Creating a SecureFiles LOB Column
As discussed previously, the default LOB architecture is SecureFiles. Having said that, I recommend that you explicitly state which LOB architecture to implement in order to avoid any confusion. As mentioned earlier, the tablespace that contains the SecureFile LOB must be ASSM managed. Here is an example that creates a SecureFiles LOB:
SQL> create table patchmain(
patch_id number
,patch_desc clob)
lob(patch_desc) store as securefile (tablespace lob_data);
Before viewing the data dictionary details regarding the LOB column, insert a record into the table to ensure that segment information is available (owing to the deferred segment allocation feature in Oracle Database 11g Release 2 and higher); for example,
SQL> insert into patchmain values(1,'clob text');
You can now verify a LOB’s architecture by querying the USER_SEGMENTS view:
With the SecureFiles architecture, you no longer need to specify the following options: CHUNK, PCTVERSION, FREEPOOLS, FREELIST, and FREELIST GROUPS.
Implementing 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 are stored in tablespaces separate from those of the table data:
SQL> CREATE TABLE patchmain(
patch_id NUMBER
,region VARCHAR2(16)
,patch_desc CLOB)
LOB(patch_desc) STORE AS (TABLESPACE patch1)
PARTITION BY LIST (REGION) (
PARTITION p1 VALUES ('EAST')
LOB(patch_desc) STORE AS SECUREFILE
(TABLESPACE patch1 COMPRESS HIGH)
TABLESPACE inv_data1
,
PARTITION p2 VALUES ('WEST')
LOB(patch_desc) STORE AS SECUREFILE
(TABLESPACE patch2 DEDUPLICATE NOCOMPRESS)
TABLESPACE inv_data2
,
PARTITION p3 VALUES (DEFAULT)
LOB(patch_desc) STORE AS SECUREFILE
(TABLESPACE patch3 COMPRESS LOW)
TABLESPACE inv_data3
);
Note that each LOB partition is created with its own storage options (see the section “Implementing SecureFiles Advanced Features,” later in this chapter, for details on SecureFiles features). You can view the details about the LOB partitions as shown:
You can also view DBA/ALL_USER_PART_LOBS for information about partitioned LOBs.
You can change the storage characteristics of a partitioned LOB column after it is created. To do so, use the ALTER TABLE ... MODIFY PARTITION statement. This example alters a LOB partition to have a high degree of compression:
SQL> alter table patchmain modify partition p2
lob (patch_desc) (compress high);
The next example modifies a partitioned LOB not to keep duplicate values (via the DEDUPLICATE clause):
SQL> alter table patchmain modify partition p3
lob (patch_desc) (deduplicate lob);
Note
Partitioning and Advanced Compression, which have been discussed in this chapter, are extra cost options that are available only with the Oracle Enterprise Edition.
Maintaining LOB Columns
The following sections describe some common maintenance tasks that are performed on LOB columns or that otherwise involve LOB columns, including moving columns between tablespaces and adding new LOB columns to a table.
Moving a LOB Column
As mentioned previously, if you create a table with a LOB column and do not specify a tablespace, then, by default, the LOB is created in the same tablespace as its table. This happens sometimes in environments in which the DBAs do not 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 tablespace separate from that of the table. Here is the basic syntax:
SQL> alter table <table_name> move lob(<lob_name>) store as (tablespace <new_tablespace);
The next example moves the LOB column to the LOB_DATA tablespace:
SQL> alter table patchmain
move lob(patch_desc)
store as securefile (tablespace lob_data);
You can verify that the LOB was moved by querying USER_LOBS:
SQL> select table_name, column_name, tablespace_name from user_lobs;
To summarize, if the LOB column is populated with large amounts of data, you almost always want to store the LOB in a tablespace separate from that of the rest of the table data. In these scenarios, the LOB data have different growth and storage requirements and are best maintained in their 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:
SQL> alter table patchmain add(inv_image blob);
This statement is fine for quickly adding a LOB column to a development environment. For anything else, you should specify the storage characteristics. For instance, this command specifies that a SecureFiles LOB be created in the LOB_DATA tablespace:
SQL> alter table patchmain add(inv_image blob)
lob(inv_image) store as securefile(tablespace lob_data);
Removing a LOB Column
You may have a scenario in which 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:
SQL> alter table patchmain rename column patch_desc to patch_desc_old;
After you determine that nobody is using the column, use the ALTER TABLE...DROP statement to drop it:
SQL> alter table patchmain drop(patch_desc_old);
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 if your recycle bin is enabled, then when you do not 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 does not 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:
SQL> create table patchmain(
patch_id number
,patch_desc clob)
lob(patch_desc) store as (tablespace lob_data cache);
You can verify the LOB caching with this query:
SQL> select table_name, column_name, cache from user_lobs;
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 writes.
NOCACHE
LOB data shouldn’t be placed in the buffer cache. This is the default for both SecureFiles and BasicFiles LOBs.
Storing LOBs In- and Out of Line
By default, up to approximately 4,000 characters of a LOB column are stored inline with the table row. If the LOB is more than 4,000 characters, then Oracle automatically stores it outside the row data. The main advantage of storing a LOB in row is that small LOBs (fewer than 4,000 characters) require less I/O, because Oracle does not have to search out of row for the LOB data.
However, storing LOB data in row is not always desirable. The disadvantage of storing LOBs in row is that the table row sizes are potentially longer. This can affect 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 the row with the DISABLE STORAGE IN ROW clause:
SQL> create table patchmain(
patch_id number
,patch_desc clob
,log_file blob)
lob(patch_desc, log_file)
store as (
tablespace lob_data
disable storage in row);
If you want to store up to 4,000 characters of a LOB in the table row, use the ENABLE STORAGE IN ROW clause when creating the table:
SQL> create table patchmain(
patch_id number
,patch_desc clob
,log_file blob)
lob(patch_desc, log_file)
store as (
tablespace lob_data
enable storage in row);
Note
The LOB locator is always stored inline with the row.
You cannot modify the LOB storage in a row after the table has been created. The only ways to alter storage in row are to move the LOB column or drop and re-create the table. This example alters the storage in row by moving the LOB column:
SQL> alter table patchmain
move lob(patch_desc)
store as (enable storage in row);
You can verify the in-row storage via the IN_ROW column of USER_LOBS:
As mentioned earlier, the SecureFiles LOB architecture allows you to compress LOB columns, eliminate duplicates, and transparently encrypt LOB data. These features provide high performance and manageability of LOB. The next few sections cover features specific to SecureFiles.
Compressing LOBs
If you are using SecureFiles 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.
Table 11-4
Degrees of Compression Available with SecureFiles 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
This example creates a CLOB column with a low degree of compression:
SQL> CREATE TABLE patchmain(
patch_id NUMBER
,patch_desc CLOB)
LOB(patch_desc) STORE AS SECUREFILE
(COMPRESS LOW)
TABLESPACE lob_data;
If a LOB has been created as a SecureFiles type, you can alter its compression level. For instance, this command changes the compression to HIGH:
SQL> alter table patchmain modify lob(patch_desc) (compress high);
If you create a LOB with compression but decide that you do not want to use the feature, you can alter the LOB to have no compression via the NOCOMPRESS clause:
SQL> alter table patchmain modify lob(patch_desc) (nocompress);
Tip
Try to enable compression, deduplication, and encryption through a CREATE TABLE statement. If you use an ALTER TABLE statement, the table is locked while the LOB is modified.
Deduplicating LOBs
If you have an application in which identical LOBs are associated with two or more rows, you should consider using the SecureFiles deduplication feature. When enabled, this instructs Oracle to check when a new LOB is inserted into a table to see whether that LOB is already stored in another row (for the same LOB column). If the LOB is already stored, then Oracle stores a pointer to the existing identical LOB. This can potentially mean huge space savings for your application.
Note
Deduplication requires the Oracle Advanced Compression option with Enterprise Edition of the Database.
This example creates a LOB column, using the deduplication feature:
SQL> CREATE TABLE patchmain(
patch_id NUMBER
,patch_desc CLOB)
LOB(patch_desc) STORE AS SECUREFILE
(DEDUPLICATE)
TABLESPACE lob_data;
To verify that the deduplication feature is in effect, run this query:
If an existing table has a SecureFiles LOB, then you can alter the column to enable deduplication:
SQL> alter table patchmain
modify lob(patch_desc) (deduplicate);
Here is another example that modifies a partitioned LOB to enable deduplication:
SQL> alter table patchmain modify partition p2
lob (patch_desc) (deduplicate lob);
If you decide that you do not want deduplication enabled, use the KEEP_DUPLICATES clause:
SQL> alter table patchmain
modify lob(patch_desc) (keep_duplicates);
Encrypting LOBs
You can transparently encrypt a SecureFiles LOB column (just like any other column). Before you use encryption features, you must set up an encryption wallet. I’ve included a sidebar at the end of this section that details how to set up a wallet.
Note
The SecureFiles encryption feature requires a license for the Oracle Advanced Security option with the Enterprise Edition of the database.
The ENCRYPT clause enables SecureFiles encryption, using Oracle Transparent Data Encryption (TDE). Traditional LOBS, when not using secure files, can be encrypted by using the utility of DBMS_CRYPTO. The following example enables encryption for the PATCH_DESC LOB column:
SQL> CREATE TABLE patchmain(
patch_id number
,patch_desc clob)
LOB(patch_desc) STORE AS SECUREFILE (encrypt)
tablespace lob_data;
When you describe the table, the LOB column now shows that encryption is in effect:
If you have already created the table, you can alter a column to enable encryption:
SQL> alter table patchmain modify
(patch_desc clob encrypt);
You can also specify an encryption algorithm; for example,
SQL> alter table patchmain modify
(patch_desc clob encrypt using '3DES168');
You can disable encryption for a SecureFiles LOB column via the DECRYPT clause:
SQL> alter table patchmain modify
(patch_desc clob decrypt);
Enabling an Oracle Wallet
An Oracle wallet is the mechanism Oracle uses to enable encryption. The wallet is an OS file that contains encryption keys. The wallet is enabled via the following steps:
1.
Modify the SQLNET.ORA file to contain the location of the wallet:
Create the wallet file (ewallet.p18) with the ALTER SYSTEM command:
SQL> alter system set encryption key identified by foo;
3.
Enable encryption:
SQL> alter system set encryption wallet open identified by foo;
See the Oracle Advanced Security Administrator’s Guide, which can be freely downloaded from the Technology Network area of the Oracle web site (http://otn.oracle.com), for full details on implementing encryption.
Migrating BasicFiles to SecureFiles
You can migrate BasicFiles 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 sections.
Creating a New Table
Here is 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 SecureFiles LOB.
SQL> create table patchmain_new(
patch_id number
,patch_desc clob)
lob(patch_desc) store as securefile (tablespace lob_data);
Next, load the newly created table with data from the old table:
SQL> insert into patchmain_new select * from patchmain;
Now, rename the tables:
SQL> rename patchmain to patchmain_old;
SQL> rename patchmain_new to patchmain;
When using this technique, be sure any grants that were pointing to the old table are reissued for the new table.
Moving a Table to SecureFiles Architecture
You can also use the ALTER TABLE...MOVE statement to redefine the storage of a LOB as a SecureFiles type; for example,
SQL> alter table patchmain
move lob(patch_desc)
store as securefile (tablespace lob_data);
You can verify that the column is now a SecureFiles type via this query:
SQL> select table_name, column_name, securefile from user_lobs;
The SECUREFILE column now has a value of YES:
TABLE_NAME COLUMN_NAME SEC
--------------- --------------- ---
PATCHMAIN PATCH_DESC YES
Migrating with Online Redefinition
You can also redefine a table while it is 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 does not have a primary key, then create one:
SQL> alter table patchmain
add constraint patchmain_pk
primary key (patch_id);
2.
Create a new table that defines the LOB column(s) as a SecureFiles type:
SQL> create table patchmain_new(
patch_id number
,patch_desc clob)
lob(patch_desc)
store as securefile (tablespace lob_data);
3.
Map the columns, and copy the data from the original table to the new table (this can take a long time if there are many rows):
In this way, you can query both the segment and the index in the DBA/ALL/USER_SEGMENTS views for LOB information.
Loading LOBs
Loading LOB data is not 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 OS 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 OS directory is /orahome/oracle/lob:
SQL> create or replace directory load_lob as '/orahome/oracle/lob';
For reference, listed next is the DDL used to create the table in which the CLOB file is loaded:
SQL> create table patchmain(
patch_id number primary key
,patch_desc clob
,patch_file blob)
lob(patch_desc, patch_file)
store as securefile (compress low) tablespace lob_data;
This example also uses a sequence named PATCH_SEQ. Here is the sequence creation script:
SQL> create sequence patch_seq;
The following bit of code uses the DBMS_LOB package to load a text file (patch.txt) into a CLOB column. In this example the table name is PATCHMAIN, and the CLOB column is PATCH_DESC:
SQL> declare
src_clb bfile; -- point to source CLOB on file system
dst_clb clob; -- destination CLOB in table
src_doc_name varchar2(300) := 'patch.txt';
src_offset integer := 1; -- where to start in the source CLOB
dst_offset integer := 1; -- where to start in the target CLOB
lang_ctx integer := dbms_lob.default_lang_ctx;
warning_msg number; -- returns warning value if bad chars
begin
src_clb := bfilename('LOAD_LOB',src_doc_name); -- assign pointer to file
--
insert into patchmain(patch_id, patch_desc) -- create LOB placeholder
values(patch_seq.nextval, empty_clob())
returning patch_desc into dst_clb;
--
dbms_lob.open(src_clb, dbms_lob.lob_readonly); -- open file
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:
SQL> set serverout on size 1000000
SQL> @clob.sql
Here is the expected output:
Wrote CLOB: patch.txt
PL/SQL procedure successfully completed.
Loading a BLOB
Loading a BLOB is similar to loading a CLOB. This example uses the directory object, table, and sequence from the previous example (which loaded a CLOB). Loading a BLOB is simpler than loading a CLOB because you do not have to specify character set information.
This example loads a file named patch.zip into the PATCH_FILE BLOB column:
SQL> declare
src_blb bfile; -- point to source BLOB on file system
dst_blb blob; -- destination BLOB in table
src_doc_name varchar2(300) := 'patch.zip';
src_offset integer := 1; -- where to start in the source BLOB
dst_offset integer := 1; -- where to start in the target BLOB
begin
src_blb := bfilename('LOAD_LOB',src_doc_name); -- assign pointer to file
--
insert into patchmain(patch_id, patch_file)
values(patch_seq.nextval, empty_blob())
returning patch_file into dst_blb; -- create LOB placeholder column first
You can place this code in a file and run it from the SQL command prompt. Here, the file that contains the code is named blob.sql:
SQL> set serverout on size 1000000
SQL> @blob.sql
Here is the expected output:
Wrote BLOB: patch.zip
PL/SQL procedure successfully completed.
Measuring LOB Space Consumed
As discussed previously, a LOB consists of an in-row lob locator, a LOB index, and a LOB segment that is made up of one or more chunks. The space used by the LOB index is usually negligible compared with 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 is a sample query:
You can also 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 BasicFiles LOBs, and the other reports on SecureFiles LOBs.
BasicFiles Space Used
Here is an example of how to call DBMS_SPACE.SPACE_USAGE for a BasicFiles 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.
Reading BFILEs
As discussed previously, a BFILE data type is simply a column in a table that stores a pointer to an OS file. A BFILE provides you with read-only access to a binary file on disk. To access a BFILE, you must first create a directory object. This is a database object that stores the location of an OS directory. The directory object makes Oracle aware of the BFILE location on disk.
This example first creates a directory object, creates a table with a BFILE column, and then uses the DBMS_LOB package to access a binary file:
SQL> create or replace directory load_lob as '/orahome/oracle/lob';
Next, a table is created that contains a BFILE data type:
SQL> create table patchmain
(patch_id number
,patch_file bfile);
For this example, a file named patch.zip is located in the aforementioned directory. You make Oracle aware of the binary file by inserting a record into the table using the directory object and the file name:
SQL> insert into patchmain values(1, bfilename('LOAD_LOB','patch.zip'));
Now, you can access the BFILE via the DBMS_LOB package. For instance, if you want to verify that the file exists or display the length of the LOB, you can do so as follows:
SQL> select dbms_lob.fileexists(bfilename('LOAD_LOB','patch.zip')) from dual;
SQL> select dbms_lob.getlength(patch_file) from patchmain;
In this manner, the binary file behaves like a BLOB. The big difference is that the binary file is not stored within the database.
Tip
See the Oracle Database PL/SQL Packages and Types Reference guide for full details on using the DBMS_LOB package. This guide is available on http://otn.oracle.com.
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). BLOBs are used to store binary files, such as images (JPEG, MPEG), movie files, sound files, and so on. If it is not feasible to store the file in the database, you can use a BFILE LOB.
Oracle provides two underlying architectures for LOBS: BasicFiles and SecureFiles. BasicFiles is the LOB architecture that has been available since Oracle version 8. The SecureFiles feature was introduced in Oracle Database 11g and is now the default value for LOBs. SecureFiles 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.
