Skip Headers

Oracle® Database Application Developer's Guide - Large Objects
10g Release 1 (10.1)

Part Number B10796-01
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page

Go to previous page
Go to next page
View PDF

Managing LOBs: Database Administration

This chapter describes administrative tasks that must be performed to setup, maintain, and use a database that contains LOBs.

This chapter contains these topics:

Database Utilities for Loading Data into LOBs

The following utilities are recommended for bulk loading data into LOB columns as part of database setup or maintenance tasks:

Using SQL*Loader to Load LOBs

There are two general techniques for using SQL*Loader to load data into LOBs:

Consider the following issues when loading LOBs with SQL*Loader:

Using SQL*Loader to Populate a BFILE Column

This section describes how to load data from files in the file system into a BFILE column.

See Also:

"Supported Environments for BFILE APIs"

Note that the BFILE datatype stores unstructured binary data in operating system files outside the database. A BFILE column or attribute stores a file locator that points to a server-side external file containing the data.


A particular file to be loaded as a BFILE does not have to actually exist at the time of loading.

The SQL*Loader assumes that the necessary DIRECTORY objects have already been created. See "Directory Object" for more information on creating directory objects.

A control file field corresponding to a BFILE column consists of column name followed by the BFILE directive.

The BFILE directive takes as arguments a DIRECTORY object name followed by a BFILE name. Both of these can be provided as string constants, or they can be dynamically sourced through some other field.

See Also:

Oracle Database Utilities for details on SQL*Loader syntax.

The following two examples illustrate the loading of BFILES.


You may need to set up the following data structures for certain examples to work:

CONNECT system/manager
CONNECT samp/samp
CREATE OR REPLACE DIRECTORY adgraphic_photo as '/tmp';
CREATE OR REPLACE DIRECTORY adgraphic_dir as '/tmp';

In the following example only the file name is specified dynamically.

Control file:

INFILE sample9.dat
INTO TABLE Print_media
(product_id  INTEGER EXTERNAL(6),
 FileName    FILLER CHAR(30),
 ad_graphic  BFILE(CONSTANT "modem_graphic_2268_21001", FileName))

Data file:

007, modem_2268.jpg,
008, monitor_3060.jpg,
009, keyboard_2056.jpg,


product_ID defaults to (255) if a size is not specified. It is mapped to the file names in the datafile. ADGRAPHIC_PHOTO is the directory where all files are stored. ADGRAPHIC_DIR is a DIRECTORY object created previously.

In the following example, the BFILE and the DIRECTORY object are specified dynamically.

Control file:

INFILE sample10.dat
INTO TABLE Print_media
 product_id INTEGER EXTERNAL(6),
 ad_graphic BFILE (DirName, FileName),
 FileName  FILLER CHAR(30),
 DirName   FILLER CHAR(30)

Data file:



DirName FILLER CHAR (30) is mapped to the datafile field containing the directory name corresponding to the file being loaded.

Using Oracle DataPump to Transfer LOB Data

You can use Oracle DataPump to transfer LOB data from one database to another.

See Also:

For details on using Oracle DataPump, refer to the Oracle Database Utilities guide.

Managing Temporary LOBs

The database keeps track of temporary LOBs in each session, and provides a v$ view called v$temporary_lobs. From the session, the application can determine which user owns the temporary LOB. As a database administrator, you can use this view to monitor and guide any emergency cleanup of temporary space used by temporary LOBs.

Managing Temporary Tablespace for Temporary LOBs

Temporary tablespace is used to store temporary LOB data. As a database administrator you control data storage resources for temporary LOB data by controlling user access to temporary tablespaces and by the creation of different temporary tablespaces.

See Also:

Refer to the Oracle Database Administrator's Guide for details on managing temporary tablespaces.

Managing BFILEs

This section describes administrative tasks for managing databases that contain BFILEs.

Rules for Using Directory Objects and BFILEs

When creating a directory object or BFILEs, ensure that the following conditions are met:

Setting Maximum Number of Open BFILEs

A limited number of BFILEs can be open simultaneously in each session. The initialization parameter, SESSION_MAX_OPEN_FILES defines an upper limit on the number of simultaneously open files in a session.

The default value for this parameter is 10. That is, you can open a maximum of 10 files at the same time in each session if the default value is used. If you want to alter this limit, then the database administrator can change the value of this parameter in the init.ora file. For example:


If the number of unclosed files reaches the SESSION_MAX_OPEN_FILES value, then you will not be able to open any more files in the session. To close all open files, use the DBMS_LOB.FILECLOSEALL call.

Changing Tablespace Storage for a LOB

As the database administrator, you can use the following techniques to change the default storage for a LOB after the table has been created: