Jay, Had the same problem when I used a script during the move of data. A look at the database tablespaces and indexes showed that the indexes all resided in TEMP and were not perminently written into the designed tablespace before the next partition started. The TEMP filled and the remaining indexes failed with the "failed to extend.." problem. I increased the size of the datafile used in the TEMP tablespace until to load was completed and then RESIZE'd it down to the max size used for the load. I figure that the loads going forward are going to be as large if not larger so I will need the additional space. My space increased from 1.4 GIG to 2.1 GIG on the datafile used as the TEMP tablespace. Other than putting some delays in the load plan to allow the updates to complete, your manual method works. ROR mª¿ªm
>>> JayMiller@Received: from CONNECT-MTA by galotterTDWaterhouse.com 01/16/02 10:50AM >>>> 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
