|Oracle9i Application Developer's Guide - Large Objects (LOBs)
Release 1 (9.0.1)
Part Number A88879-01
Managing LOBs , 10 of 12
LOB columns are subject to the following restrictions:
WHEREclauses of queries or in functions of the
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;
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 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:
However, the following statement is not supported:
BYclause of a query, or in the
BYclause of a query or in an aggregate function.
UNIQUEstatement or in a join. However, you can specify a LOB attribute of an object type column in a
DISTINCTstatement or in a query that uses the
MINUSset operator if the column's object type has a
ORDERfunction defined on it.
NCLOBas an attribute of an object type when creating a table. However, you can specify
NCLOBparameters in methods.
ROWDML trigger, you can read the
:oldvalue of the LOB, but you cannot read the
:newvalue. However, for
OFDML triggers, you can read both the
UPDATEDML trigger on a LOB column.
UPDATEoperation, 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
SELECToperation, you can bind up to 4000 bytes of data to LOB columns.
LONGand LOB columns, you cannot bind more than 4000 bytes of data to both the
LONGand LOB columns in the same SQL statement. However, you can bind more than 4000 bytes of data to either the
LONGor the LOB column.
The first extent of any segment requries 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 about this especially if your tablespace has lots of free space!