SQL Loader Direct Load - Problem with Index Rebuild

From: Miller, Jay <JayMiller_at_TDWaterhouse.com>
Date: Wed, 16 Jan 2002 08:06:19 -0800
Message-ID: <F001.003F1680.20020116075052@fatcity.com>

Hi,

Okay, this is the second time this has happened and while I have a guess I'd appreciate any feedback on the issue. Last night during a direct load to one of our datawarehouse tables 4 of the index partition rebuilds failed with the following errors (the remaining partitions were fine, as were all the other indexes):

index EIS.IDX_BKP_TRANS_DATE partition BOOK2001OCT was made unusable due to: ORA-01652: unable to extend temp segment by 320 in tablespace TS_BOOKKEEPING_FACT_IDX
index EIS.IDX_BKP_TRANS_DATE partition BOOK2001NOV was made unusable due to: ORA-01658: unable to create INITIAL extent for segment in tablespace TS_BOOKKEEPING_FACT_IDX
index EIS.IDX_BKP_TRANS_DATE partition BOOK2001DEC was made unusable due to: ORA-01658: unable to create INITIAL extent for segment in tablespace TS_BOOKKEEPING_FACT_IDX
index EIS.IDX_BKP_TRANS_DATE partition BOOK2002JAN was made unusable due to: ORA-01658: unable to create INITIAL extent for segment in tablespace TS_BOOKKEEPING_FACT_IDX I was able to manually rebuild the index partitions with no problem.

There are currently 1,881 free extents of the size 320 blocks in that tablespace. The entire index (including all partitions) only takes up 473 extents. We have degrees=1 on the index.

There's a metalink Forum thread (ORA 1652 During Direct Load) where a similar problem occurred. It seems to imply each index may be claiming 2x it's required space while being rebuilt and that SMON might not clean up that space right away. This could certainly have caused there to be not enough space available if it required the tablespace to be 2x the size of *all* the indexes. In that case the fact that SMON cleaned it up before I did the manual rebuild would explain why the latter worked. Looking at the tablespace I see that it is currently almost 2x the size so it's possible that's why I had avoided the problem for the last few months.

Has anyone else had this problem and is there a workaround short of dropping the indexes before the load and recreating them? I'd rather avoid that since it's usually only a few partitions that need to be rebuilt.

I'm on Oracle 8.1.6.3, Solaris 2.6.

Thanks,
Jay Miller

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Miller, Jay
INET: JayMiller_at_TDWaterhouse.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Wed Jan 16 2002 - 10:06:19 CST