You have to keep a few restrictions in mind while working with LOB data.
LOB columns are subject to the following rules and restrictions:
- You cannot specify a LOB as a primary key column.
- You cannot specify LOB columns in the
BYclause of a query, the
BYclause of a query, or an aggregate function.
- You cannot specify a LOB column in a
UNIQUEstatement or in a join. However, you can specify a LOB attribute of an object type column in a
DISTINCTstatement, a query that uses the
UNION, or a
MINUSset operator if the object type of the column has a
ORDERfunction defined on it.
- Clusters cannot contain LOBs, either as key or nonkey columns.
- Even though compressed
VARRAYdata types are supported, they are less performant.
- The following data structures are supported only as temporary instances. You cannot store these instances in database tables:
VARRAYof any LOB type
VARRAYof any type containing a LOB type, such as an object type with a LOB attribute
ANYDATAof any LOB type
ANYDATAof any type containing a LOB
- The first (
INITIAL) extent of a LOB segment must contain at least three database blocks.
- The minimum extent size is 14 blocks. For an 8K block size (the default), this is equivalent to 112K.
- When creating an
AFTER UPDATEDML trigger, you cannot specify a LOB column in the
OFclause. For a table on which you have defined an
AFTER UPDATE DMLtrigger, if you use OCI functions or the
DBMS_LOBpackage to change the value of a LOB column or the LOB attribute of an object type column, the database does not fire the DML trigger.
- You cannot specify a LOB column as part of an index key. However, you can specify a LOB column in the indextype specification of a functional or domain index. In addition, Oracle Text lets you define an index on a
- In SQL Loader, a field read from a LOB cannot be used as an argument to a clause.
- Case-insensitive searches on
CLOBcolumns often do not succeed. If you perform the following case-insensitive search on a
ALTER SESSION SET NLS_COMP=LINGUISTIC; ALTER SESSION SET NLS_SORT=BINARY_CI; SELECT * FROM ci_test WHERE LOWER(clob_col) LIKE 'aa%';
The select fails without the
LOWERfunction. You can perform case-insensitive searches with Oracle Text or the
- Restrictions on SQL Operations on LOBs
- Guidelines and Restrictions for Implicit Conversions with LOBs
- Restrictions when using remote LOB locators
- Restrictions on Mounted File Systems
- Restrictions on Types of Files Stored at DBFS Mount Points
- Restrictions on Index Organized Tables with LOB Columns
- Restrictions on Migrating LOBs with Data Pump