This chapter describes administrative tasks that must be performed to set up, maintain, and use a database that contains LOBs.
This chapter contains these topics:
The following utilities are recommended for bulk loading data into LOB columns as part of database setup or maintenance tasks:
SQL*Loader
Oracle DataPump
Note: Application Developers: If you are loading data into a LOB in your application, then using the LOB APIs is recommended. See Chapter 14, " LOB APIs for Basic Operations" for details on APIs that allow you to load LOBs from files. |
There are two general techniques for using SQL*Loader to load data into LOBs:
Loading data from a primary data file
Loading from a secondary data file using LOBFILEs
Consider the following issues when loading LOBs with SQL*Loader:
For SQL*Loader conventional path loads, failure to load a particular LOB does not result in the rejection of the record containing that LOB; instead, the record ends up containing an empty LOB.
For SQL*Loader direct-path loads, the LOB could be empty or truncated. LOBs are sent in pieces to the server for loading. If there is an error, then the LOB piece with the error is discarded and the rest of that LOB is not loaded. In other words, if the entire LOB with the error is contained in the first piece, then that LOB column will either be empty or truncated.
When loading from LOBFILEs specify the maximum length of the field corresponding to a LOB-type column. If the maximum length is specified, then it is taken as a hint to help optimize memory usage. It is important that the maximum length specification does not underestimate the true maximum length.
When using SQL*Loader direct-path load, loading LOBs can take up substantial memory. If the message "SQL*Loader 700 (out of memory)" appears when loading LOBs, then internal code is probably batching up more rows in each load call than can be supported by your operating system and process memory. A work-around is to use the ROWS option to read a smaller number of rows in each data save.
You can also use the Direct Path API to load LOBs.
Using LOBFILEs is recommended when loading columns containing XML data in CLOB
s or XMLType
columns. Whether you perform a direct-path load or a conventional path load with SQL*Loader depends on whether you need to validate XML documents upon loading.
If the XML document must be validated upon loading, then use conventional path load.
If it is not necessary to ensure that the XML document is valid or you can safely assume that the XML document is valid, then you can perform a direct-path load. Performance is higher when you use direct-path load because the overhead of XML validation is incurred.
A conventional path load executes SQL INSERT statements to populate tables in an Oracle database. A direct path load eliminates much of the Oracle database overhead by formatting Oracle data blocks and writing the data blocks directly to the database files.
A direct-path load does not compete with other users for database resources, so it can usually load data at near disk speed. Considerations inherent to direct path loads, such as restrictions, security, and backup implications, are discussed in Oracle Database Utilities.
Tables to be loaded must already exist in the database. SQL*Loader never creates tables. It loads existing tables that either already contain data or are empty.
The following privileges are required for a load:
You must have INSERT privileges on the table to be loaded.
You must have DELETE privilege on the table to be loaded, when using the REPLACE or TRUNCATE option to empty out the old data before loading the new data in its place.
See Also: For details on using SQL*Loader to load LOBs and other details on SQL*Loader usage, refer to the Oracle Database Utilities guide. |
This section describes how to load data from files in the file system into a BFILE
column.
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.
Note: A particular file to be loaded as aBFILE does not have to actually exist at the time of loading. |
SQL*Loader assumes that the necessary DIRECTORY
objects have already been created.
See Also: See "Directory Objects" and the sections following it 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.
The following two examples illustrate the loading of BFILES
.
Note: You may need to set up the following data structures for certain examples to work:CONNECT system/manager GRANT CREATE ANY DIRECTORY to samp; 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:
LOAD DATA INFILE sample9.dat INTO TABLE Print_media FIELDS TERMINATED BY ',' (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,
Note: product_ID defaults to (255) if a size is not specified. It is mapped to the file names in the data file. 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:
LOAD DATA INFILE sample10.dat INTO TABLE Print_media FIELDS TERMINATED BY ',' ( product_id INTEGER EXTERNAL(6), ad_graphic BFILE (DirName, FileName), FileName FILLER CHAR(30), DirName FILLER CHAR(30) )
Data file:
007,monitor_3060.jpg,ADGRAPHIC_PHOTO, 008,modem_2268.jpg,ADGRAPHIC_PHOTO, 009,keyboard_2056.jpg,ADGRAPHIC_DIR,
Note: DirName FILLER CHAR (30) is mapped to the data file field containing the directory name corresponding to the file being loaded. |
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.
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. |
This section describes administrative tasks for managing databases that contain BFILE
s.
When creating a directory object or BFILE
s, ensure that the following conditions are met:
The operating system file must not be a symbolic or hard link.
The operating system directory path named in the Oracle DIRECTORY object must be an existing operating system directory path.
The operating system directory path named in the Oracle DIRECTORY object should not contain any symbolic links in its components.
A limited number of BFILE
s 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:
SESSION_MAX_OPEN_FILES=20
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.
As the database administrator, you can use the following techniques to change the default storage for a LOB after the table has been created:
Using ALTER TABLE... MODIFY: You can change LOB tablespace storage as follows:
Note:
|
ALTER TABLE test MODIFY LOB (lob1) STORAGE ( NEXT 4M MAXEXTENTS 100 PCTINCREASE 50 )
Using ALTER TABLE... MOVE: You can also use the MOVE clause of the ALTER TABLE statement to change LOB tablespace storage. For example:
ALTER TABLE test MOVE TABLESPACE tbs1 LOB (lob1, lob2) STORE AS ( TABLESPACE tbs2 DISABLE STORAGE IN ROW);