4
Managing LOBs

This chapter describes the following topics:

• Rules for using Directory Objects and BFILEs
• DBA Actions Required Prior to Working with LOBs
• Managing Temporary LOBs
• Using SQL*Loader to Load LOBs
• Inline versus Out-of-Line LOBs
• Loading InLine and Out-Of-Line Data into Internal LOBs Using SQL*Loader
• Loading Inline LOB Data
• Loading Out-Of-Line LOB Data
• SQL*Loader LOB Loading Tips
• LOB Restrictions

Rules for using Directory Objects and BFILEs

When creating a directory object or BFILEs, 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 OS directory path.
• The operating system directory path named in the Oracle DIRECTORY object should not contain any symbolic links in its components.

DBA Actions Required Prior to Working with LOBs

This section describes actions that you or your database administrator must take prior to working with LOBs.

Set 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 utilized. If you want to alter this limit, 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.

Using SQL DML for Basic Operations on LOBs

SQL Data Manipulation Language (DML) includes basic operations, such as, INSERT, UPDATE, DELETE -- that let you make changes to the entire value of internal LOBs within Oracle RDBMS.

• Internal LOBs: To work with parts of internal LOBs, you will need to use one of the interfaces described in Chapter 3, "LOB Support in Different Programmatic Environments", that have been developed to handle more complex requirements. Alternatively, you can perform string operations on CLOBs using SQL VARCHAR2 functions.

  See Also: Chapter 7, "Modeling and Design"

  For use case examples refer to the following sections in Chapter 10, "Internal Persistent LOBs":

  • INSERT:
    • "Inserting a LOB Value using EMPTY_CLOB() or EMPTY_BLOB()"
    • "Inserting a Row by Selecting a LOB From Another Table"
    • "Inserting a Row by Initializing a LOB Locator Bind Variable"
  • UPDATE:
    • "Updating a LOB with EMPTY_CLOB() or EMPTY_BLOB()"
    • "Updating a Row by Selecting a LOB From Another Table"
    • "Updating by Initializing a LOB Locator Bind Variable"
  • DELETE:
    • "Deleting the Row of a Table Containing a LOB"
• External LOBs (BFILEs): Oracle8i supports read-only operations on external LOBs. See Chapter 12, "External LOBs (BFILEs)":
  • INSERT:
    • "INSERT a Row Using BFILENAME()"
    • "INSERT a BFILE Row by Selecting a BFILE From Another Table"
    • "Inserting a Row With BFILE by Initializing a BFILE Locator"
• UPDATE: You can use the following methods to UPDATE or 'write to' a BFILE:
  • "Updating a BFILE Using BFILENAME()"
  • "Updating a BFILE by Selecting a BFILE From Another Table"
  • "Updating a BFILE by Initializing a BFILE Locator"
• DELETE:
  • "Deleting the Row of a Table Containing a BFILE"

Changing Tablespace Storage for a LOB

It is possible to change the default storage for a LOB after the table has been created.

Oracle8 Release 8.0.4.3

To move the CLOB column from tablespace A to tablespace B, in Oracle8 release 8.0.4.3, requires the following statement:

ALTER TABLE test lob(test) STORE AS (tablespace tools);

However, this returns the following error message:

ORA-02210: no options specified for ALTER TABLE

Oracle8i and Oracle9i

• Using ALTER TABLE... MODIFY: You can change LOB tablespace storage as follows:

  Note: The ALTER TABLE syntax for modifying an existing LOB column uses the MODIFY LOB clause, not the LOB...STORE AS clause. The LOB...STORE AS clause is only for newly added LOB columns. There are two kinds of LOB storage clauses: LOB_storage_clause and modify_LOB_storage_clause. In the ALTER TABLE MODIFY LOB statement, you can only specify modify_LOB_storage_clause.

  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);
  
  

Managing Temporary LOBs

Management and security issues of temporary LOBs are discussed in Chapter 11, "Temporary LOBs",

• Managing Temporary LOBs
• Security Issues with Temporary LOBs

Using SQL*Loader to Load LOBs

You can use SQL*Loader to bulk load LOBs. See "Loading LOBs" in Oracle9i Database Utilities for details on using SQL*Loader control file data definition language (DDL) to load LOB types.

Data loaded into LOBs can be lengthy and it is likely that you will want to have the data out- of-line from the rest of the data. LOBFILES provide a method to separate lengthy data.

LOBFILES

LOBFILES are simple datafiles that facilitate LOB loading. LOBFILEs are distinguished from primary datafiles in that in LOBFILEs there is no concept of a record. In LOBFILEs the data is of any of the following types:

• Predetermined size fields (fixed length fields)
• Delimited fields, for example, TERMINATED BY or ENCLOSED BY

  Note: The clause PRESERVE BLANKS is not applicable to fields read from a LOBFILE.

• Length-Value pair fields (variable length fields) -- VARRAW, VARCHAR, or VARCHARC loader datatypes are used for loading from this type of fields.
• A single LOB field into which the entire contents of a file can be read.

  Note: A field read from a LOBFILE cannot be used as an argument to a clause (for example, the NULLIF clause).

Inline versus Out-of-Line LOBs

Inline LOBs are LOBs whose value comes from the primary data file.

Out-of-Line LOBs are LOBs whose value comes from LOBFILEs.

Loading InLine and Out-Of-Line Data into Internal LOBs Using SQL*Loader

The following sections describe procedures for loading differently formatted inline and out-of-line data into internal LOBs:

• Loading InLine LOB Data
  • Loading Inline LOB Data in Predetermined Size Fields
  • Loading Inline LOB Data in Delimited Fields
  • Loading Inline LOB Data in Length-Value Pair Fields
• Loading Out-Of-Line LOB Data
  • Loading One LOB Per File
  • Loading Out-of-Line LOB Data in Predetermined Size Fields
  • Loading Out-of-Line LOB Data in Delimited Fields
  • Loading Out-of-Line LOB Data in Length-Value Pair Fields

Other topics discussed are

• SQL*Loader LOB Loading Tips

SQL*Loader Performance: Loading Into Internal LOBs

See Table 4-1, "SQL*Loader Performance: Loading Data Into Internal LOBs" for the relative performance when using the preceding methods of loading data into internal LOBs.

Table 4-1 SQL*Loader Performance: Loading Data Into Internal LOBs

Loading Method For In-Line or Out-Of-Line Data	Relative Performance
In Predetermined Size Fields	Highest
In Delimited Fields	Slower
In Length Value-Pair Fields	High
One LOB Per File	High

Loading Inline LOB Data

• Loading Inline LOB Data in Predetermined Size Fields
• Loading Inline LOB Data in Delimited Fields
• Loading Inline LOB Data in Length-Value Pair Fields

Loading Inline LOB Data in Predetermined Size Fields

This is a very fast and simple way to load LOBs. Unfortunately, the LOBs to be loaded are not usually the same size.

To load LOBs using this format, use either CHAR or RAW as the loading datatype. For example:

Control File

LOAD DATA 
INFILE 'sample.dat' "fix 21" 
INTO TABLE Multimedia_tab 
    APPEND
    (Clip_ID POSITION(1:3) INTEGER EXTERNAL, 
    Story POSITION(5:20)   CHAR DEFAULTIF Story=BLANKS) 

Data File (sample.dat)

007 Once upon a time

If the datafield containing the story is empty, then an empty LOB instead of a NULL LOB is produced. A NULL LOB is produced only if the NULLIF clause is used instead of the DEFAULTIF clause. You can use loader datatypes other than CHAR to load LOBs. Use the RAW datatype when loading BLOBs.

Loading Inline LOB Data in Delimited Fields

Loading different size LOBs in the same column (that is, datafile field) is not a problem. The trade-off for this added flexibility is performance. Loading in this format is somewhat slower because the loader has to scan through the data, looking for the delimiter string. For example:

Control File

LOAD DATA
INFILE 'sample1.dat' "str '<endrec>\n'"
INTO TABLE Multimedia_tab
FIELDS TERMINATED BY ','
(
Clip_ID   CHAR(3),
 Story    CHAR(507) ENCLOSED BY '<startlob>' AND '<endlob>'
)

Data File (sample1.dat)

007,    <startlob>     Once upon a time,The end. '<endlob>' '<endrec>'
008,    <startlob>     Once upon another time ....The end. '<endlob>' '<endrec>'

Loading Inline LOB Data in Length-Value Pair Fields

You could use VARCHAR (see Oracle9i Database Utilities), VARCHARC, or VARRAW datatypes to load LOB data organized in this way. Note that this method of loading produces better performance over the previous method, however, it removes some of the flexibility, that is, it requires you to know the LOB length for each LOB before loading. For example:

Control File

LOAD DATA
INFILE 'sample2.dat' "str '<endrec>\r\n'"
INTO TABLE Multimedia_tab
APPEND
FIELDS TERMINATED BY ','
(
Clip_ID  INTEGER EXTERNAL (3),
 Story   VARCHARC (3, 500)
)

Data File (sample2.dat)

007,041    Once upon a time...  ....  The end.  <endrec>
008,000 <endrec>

Loading Out-Of-Line LOB Data

This section describes the following topics:

• Loading One LOB Per File
• Loading Out-of-Line LOB Data in Predetermined Size Fields
• Loading Out-of-Line LOB Data in Delimited Fields
• Loading Out-of-Line LOB Data in Length-Value Pair Fields

As mentioned earlier, LOB data can be so large that it is reasonable to want to load it from secondary datafile(s).

In LOBFILEs, LOB data instances are still thought to be in fields (predetermined size, delimited, length-value), but these fields are not organized into records (the concept of a record does not exist within LOBFILES); thus, the processing overhead of dealing with records is avoided. This type of organization of data is ideal for LOB loading.

Loading One LOB Per File

Each LOBFILE contains a single LOB. For example:

Control File

LOAD DATA
INFILE 'sample3.dat'
INTO TABLE Multimedia_tab
REPLACE
FIELDS TERMINATED BY ','
(
 Clip_ID        INTEGER EXTERNAL(5),
 ext_FileName   FILLER CHAR(40),
 Story          LOBFILE(ext_FileName) TERMINATED BY EOF
)

Data File (sample3.dat)

007,FirstStory.txt,
008,/tmp/SecondStory.txt, 

Secondary Data File (FirstStory.txt)

Once upon a time ...
The end.

Secondary Data File (SecondStory.txt)

Once upon another time ....
The end.

Loading Out-of-Line LOB Data in Predetermined Size Fields

In the control file, the size of the LOBs to be loaded into a particular column is specified. During the load, any LOB data loaded into that column is assumed to be the specified size. The predetermined size of the fields allows the dataparser to perform very well. Unfortunately, it is often hard to guarantee that all the LOBs are the same size. For example:

Control File

LOAD DATA 
INFILE 'sample4.dat'
INTO TABLE Multimedia_tab
FIELDS TERMINATED BY ','
(
 Clip_ID    INTEGER EXTERNAL(5),
 Story      LOBFILE (CONSTANT 'FirstStory1.txt') CHAR(32)
)

Data File (sample4.dat)

007,
008,

Secondary Data File (FirstStory1.txt)

Once upon the time ...
The end,
Upon another time ...
The end, 

Loading Out-of-Line LOB Data in Delimited Fields

LOB data instances in LOBFILE files are delimited. In this format, loading different size LOBs into the same column is not a problem. The trade-off for this added flexibility is performance. Loading in this format is somewhat slower because the loader has to scan through the data, looking for the delimiter string. For example:

Control File

LOAD DATA
INFILE 'sample5.dat'
INTO TABLE Multimedia_tab
FIELDS  TERMINATED BY ','
(Clip_ID    INTEGER EXTERNAL(5),
Story       LOBFILE (CONSTANT 'FirstStory2.txt') CHAR(2000)
TERMINATED BY "<endlob>\n")

Windows users: Terminate the control file using:

TERMINATED BY "<endlob>\r\n")

Data File (sample5.dat)

007,
008,

Secondary Data File (FirstStory2.txt)

Once upon a time...
The end.<endlob>
Once upon another time...
The end.<endlob>

Loading Out-of-Line LOB Data in Length-Value Pair Fields

Each LOB in the LOBFILE is preceded by its length. You can use VARCHAR (see Oracle8 Utilities), VARCHARC, or VARRAW datatypes to load LOB data organized in this way. The controllable syntax for loading length-value pair specified LOBs is quite simple.

Note that this method of loading performs better than the previous one, but at the same time it takes some of the flexibility away, that is, it requires that you know the length of each LOB before loading. For example:

Control File

LOAD DATA
INFILE 'sample6.dat'
INTO TABLE Multimedia_tab
FIELDS TERMINATED BY ','
(
Clip_ID     INTEGER EXTERNAL(5),
Story       LOBFILE (CONSTANT 'FirstStory3.txt') VARCHARC(4,2000)
)

Data File (sample6.dat)

007,
008,

Secondary Data File (FirstStory3.txt)

0031
Once upon a time ... The end.
0000

SQL*Loader LOB Loading Tips

• 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, 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, 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, internal code is probably batching up more rows in each load call that which can be supported by your operating system and process memory. A workaround is to use the ROWS option to read a smaller number of rows in each data save.
• Yo can also use the Direct Path API to load LOBs.

  See Also: Oracle9i Database Utilities Chapters 7 and 9. Chapter 9, "LOBS: Best Practices", Using SQL*Loader.

LOB Restrictions

This section provides details on LOB restrictions.

LOB columns are subject to the following restrictions:

• Distributed LOBs are not supported. Therefore, you cannot use a remote locator in SELECT or WHERE clauses of queries or in functions of the DBMS_LOB package.

  The following syntax is not supported for LOBs:

  SELECT lobcol FROM table1@remote_site;
  INSERT INTO lobtable SELECT type1.lobattr FROM 
      table1@remote_site;
  SELECT DBMS_LOB.getlength(lobcol) FROM table1@remote_site;
  
  

  (This statement produces error: ORA-22992 cannot use LOB locators selected from remote tables.)

  However, you can use a remote locator in others parts of queries that reference LOBs. The following syntax is supported on remote LOB columns:

  CREATE TABLE t AS SELECT * FROM table1@remote_site;
  INSERT INTO t SELECT * FROM table1@remote_site;
  UPDATE t SET lobcol = (SELECT lobcol FROM table1@remote_site);
  INSERT INTO table1@remote_site ...
  UPDATE table1@remote_site ...
  DELETE FROM table1@remote_site ...
  
  

  For the first three types of statement, which contain subqueries, only standalone LOB columns are allowed in the select list. SQL functions or DBMS_LOB APIs on LOBs are not supported. For example, the following statement is supported:

  CREATE TABLE AS SELECT clob_col FROM tab@dbs2; 
  
  

  However, the following statement is not supported:

  CREATE TABLE AS SELECT dbms_lob.substr(clob_col) from tab@dbs2; 
  
  

• Clusters cannot contain LOBs, either as key or non-key columns. This produces error, ORA-02335: invalid datatype for cluster column.
• You cannot create a VARRAY of LOBs. This produces error, ORA-02348: cannot create VARRAY column with embedded LOB.
• You cannot specify LOB columns in the ORDER BY clause of a query, or in the GROUP BY clause of a query or in an aggregate function. This produces error, ORA-00932: inconsistent datatypes.
• You cannot specify a LOB column in a SELECT... DISTINCT or SELECT... UNIQUE statement or in a join. However, you can specify a LOB attribute of an object type column in a SELECT... DISTINCT statement or in a query that uses the UNION or MINUS set operator if the column's object type has a MAP or ORDER function defined on it.
• You cannot specify an NCLOB as an attribute of an object type when creating a table. However, you can specify NCLOB parameters in methods.
• You cannot specify LOB columns in ANALYZE... COMPUTE or ANALYZE... ESTIMATE statements.
• You cannot define an UPDATE DML trigger on a LOB column.
• You cannot specify a LOB as a primary key column.
• You cannot specify a LOB column as part of an index key. However, you can specify a LOB column in the function of a function-based index or in the indextype specification of a domain index. In addition, Oracle Text lets you define an index on a CLOB column.

  See Also: See Oracle9i Data Cartridge Developer's Guide for more information about defining triggers on domain indexes

• In an INSERT or UPDATE operation, you can bind data of any size to a LOB column, but you cannot bind data to a LOB attribute of an object type. In an INSERT... AS SELECT operation, you can bind up to 4000 bytes of data to LOB columns.

  See Also: Chapter 7, "Modeling and Design", "SQL Semantics Support for LOBs" . The "Keywords and Parameters" section of individual SQL statements in Oracle9i SQL Reference for additional semantics for the use of LOBs

• If a table has both LONG and LOB columns, you cannot bind more than 4000 bytes of data to both the LONG and LOB columns in the same SQL statement. However, you can bind more than 4000 bytes of data to either the LONG or the LOB column.
• First Extent of Any LOB Segment Must be At Least 3 Blocks. The first extent (or initial extent) of a LOB segment requires at least 3 blocks. This restriction has existed since LOBs were implemented in Oracle Release 8.0.

  The first extent of any segment requires at least 2 blocks (if FREELIST GROUPS was 0). That is, the initial extent size of the segment should be at least 2 blocks. LOBs segments are different because they need at least 3 blocks in the first extent. If you try to create a LOB segment in a permanent dictionary managed tablespace with initial = 2 blocks, it will still work because it is possible for segments in permanent dictionary managed tablespaces to override tablespaces' default storage setting.

  But if uniform locally managed tablespaces or dictionary managed tablespaces of the temporary type, or locally managed temporary tablespaces have an extent size of 2 blocks, LOB segments cannot be created in these tablespaces. This is because in these tablespace types, extent sizes are fixed and tablespaces' default storage setting is not ignored.

  You will get a message on trying to create the LOB segment: ORA-3237 "initial extent of specified size cannot be allocated". You could be confused by this error, especially if your tablespace has lots of free space.

• BFILEs in Shared Server (Multi-Threaded Server -- MTS) Mode. Oracle9i does not support session migration for BFILEs in Shared Server (Multi-Threaded Server -- MTS) mode. This implies that operations on open BFILEs can persist beyond the end of a call to a shared server. In shared server sessions, BFILE operations will be bound to one shared server, they cannot migrate from one server to another. This restriction will be removed in a forthcoming release.

  Notes: Oracle8i Release 2 (8.1.6) and higher support the CACHE READS setting for LOBs. If you have such LOBs and you downgrade to an earlier release, Oracle generates a warning and converts the LOBs from CACHE READS to CACHE LOGGING. You can subsequently alter the LOBs to either NOCACHE LOGGING or NOCACHE NOLOGGING. For more information see Chapter 7, "Modeling and Design", Chapter , "CACHE / NOCACHE / CACHE READS" . For a table on which you have defined a DML trigger, if you use OCI functions or DBMS_LOB routines to change the value of a LOB column or the LOB attribute of an object type column, Oracle does not fire the DML trigger.

Other general LOB restrictions include the following:

• LOBs are only supported in range partitioned index-organized tables. See "Partitioned Index-Organized Tables and LOBs" for more information.
• Client PL/SQL procedures cannot call DBMS_LOB routines. See "PL/SQL - LOB Guidelines" for more information.
• When using DBMS_LOB.LOADFROMFILE, you cannot specify an amount parameter value larger than the size of the BFILE. See "PL/SQL - LOB Guidelines" for more information.
• When using DBMS_LOB.READ, the amount parameter can be larger than the size of the data. See "PL/SQL - LOB Guidelines" for more information.
• A field read from a LOBFILE cannot be used as an argument to a clause. See "LOBFILES" for more information.

  See Also: "SQL*Loader LOB Loading Tips". Chapter 5, "Large Objects: Advanced Topics", "Restrictions for LOBs in Partitioned Index-Organized Tables" .