LOB Segments and Indexes

Evolution of LOB

Oracle 8i
Oracle 8iR2
Oracle 9i
Oracle 9iR2
Temporary LOBs Temporary LOBs
Varying width CLOB and NCLOB support Varying width CLOB and NCLOB support
Support for LOBs in partitioned tables Support for LOBs in partitioned tables
New API for LOBs (open/close/isopen, writeappend, getchunksize) New API for LOBs (open/close/isopen, writeappend, getchunksize)
Support for LOBs in non-partitioned index-organized tables Support for LOBs in non-partitioned index-organized tables
Copying the value of a LONG to a LOB Copying the value of a LONG to a LOB
CACHE READS
A CACHE READS option for LOB columns.
4,000 byte restriction removed
The 4,000 byte restriction for bind variables binding to an internal LOB was removed.
Binding More Than 4,000 Bytes of Data
Oracle supports binding more than 4,000 bytes of data to internal LOB columns in INSERT and UPDATE statements.
If a table has LONG and LOB columns, you can bind more than 4,000 bytes of data for either the LONG column or the LOB columns, but not both in the same statement.
LONG-to-LOB Migration API
Using SQL Semantics with LOBs.
These semantics are recommended when using small-sized LOBs (~ 10-100KB).
Using Oracle C++ Call Interface (OCCI) with LOBs
New JDBC LOB Functionality
Support for LOBs in Partitioned Index-Organized Tables
Using OLEDB and LOBsPersistent LOBs: READ/WRITE through the rowset.BFILEs: READ-ONLY through the rowset.
DBMS_LOB.LOADBLOBFROMFILE
DBMS_LOB.LOADCLOBFROMFILE
Restrictions removed
01. There is now DML BEFORE ROW Trigger :new support for LOBs. This means that triggers on LOBs follow the same rules as triggers on any other type of column.
02. You can now create LOB columns in locally managed tablespaces.
03. You can now store LOBs in AUTO segment-managed tablespaces.
04. NCLOB parameters are now allowed as attributes in object types.
05. Partitioned Index Organized Tables (PIOT) are now supported.
06.Client-side PL/SQL DBMS_LOB procedures are now supported.
07. Prior to Release 9.2, in a PL/SQL trigger body of an BEFORE ROW DML trigger, you could read the :old value of the LOB, but you could not read the :new value.
08.In releases prior to 9.2, if a view with a LOB column has an INSTEAD OF TRIGGER, then you cannot specify a string INSERT/UPDATE into the LOB column. This restriction is removed in release 9.2. For example:CREATE TABLE t(a LONG);CREATE VIEW v AS SELECT * FROM t;CREATE TRIGGER trig INSTEAD OF insert on v….;ALTER TABLE t MODIFY (a CLOB);INSERT INTO v VALUES (‘abc’); /* works now */

References:
http://www.idevelopment.info/data/Oracle/DBA_tips/LOBs/LOBS_2.shtml
http://www.orafaq.com/papers/lobs.doc
Doc Id 130814.1 Matalink – How to move LOB Data to Another Tablespace
Doc Id 198160.1 Metalink – Summary note to LOB’s/BLOB’s/CLOB’s/NCLOB’s and BFILES
Doc Id 61737.1 Metalink – How to Manipulate Large Objects Using DBMS_LOB Package
Doc Id 119775.1 Metalink – Configuring the LOB Storage of Varrays in Objecttypes
Doc Id 162345.1 Metalink – Storage, Read-consistency and Rollback