Re: Imaginary indexes created when setting CLOBs to null?

The index name shows that this is the LOBINDEX. Each out of line LOB item is broken into CHUNKs, and a bitmap in the LOB (DATA) SEGMENT is used to map where in the SEGMENT there are free spaces that can take the CHUNKS, but there is also a LOBINDEX stored in the same tablespace that links together all the CHUNKS for a single LOB item.

Because of the way Oracle handles read-consistency of LOBs by versioning the LOBINDEX whilst leaving the data in place (until roughly PCTVERSION of the segment's current storage is holding old data), it is necessary for a 'convert to null' of a LOB to add entries to the LOBINDEX.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Now available One-day tutorials:
  Cost Based Optimisation
  Trouble-shooting and Tuning
  Indexing Strategies

(see http://www.jlcomp.demon.co.uk/tutorial.html )

____UK_______April 8th
____UK_______April 22nd

____Denmark May 21-23rd

____USA_(FL)_May 2nd

Next dates for the 3-day seminar:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____UK_(Manchester)_May
____USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

• Original Message ----- From: <JayMiller_at_TDWaterhouse.com> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: 19 March 2003 21:36 Subject: Imaginary indexes created when setting CLOBs to null?

> We've been testing a new application in development using CLOBS.
The
> developer contacted me with following error:
>
> 1]: (Error): ORA-01654: unable to extend index
> YURIY.SYS_IL0000185466C00023$$ by 256 in tablespace TS_EMAIL_CLOB
>
> The odd things here are that
>
> a) It occurred while updating the CLOB column to null (why would
setting
> something to null *increase* the size of an index?)
> b) The named index does not exist (implying it was something Oracle
might
> have created on the fly?)
>
> I increased the tablespace size and the problem went away but I'm
trying to
> figure out what happened.
>
> The tablespace is one assigned to the CLOB segment.
Received on Thu Mar 20 2003 - 02:09:18 CST