Re: What to do when"index made unusable by..." on loading of large table?

kgrigg_at_acxiom.com wrote:

1. It looks like you're using the a (small) temp segment in the SYSTEM tablespace. This is not reccomended.

If you have a larger TEMP tablespace, this should be used as default for the user running the load (ALTER PROFILE ...) If you don't, then create it !

2) The indices can be rebuilt with ALTER INDEX index_name REBUILD

>Hello All! I just ran a large load via direct load (approx 1.7 million
>records). I had added large datafile to the table space which contained
>the table and there was no problem there. I hadn't thought about the
>indexes, though and got this message at the end of the load:
>
>The following index(es) on table ZIP_PLUS_4 were processed:
>index KGRIGG.ZIP_PLUS_4_STATE_IND was made unusable due to:
>ORA-01652: unable to extend temp segment by 5394 in tablespace SYSTEM
>index KGRIGG.ZIP_PLUS_4_PK_I was made unusable due to:
>ORA-01652: unable to extend temp segment by 5394 in tablespace SYSTEM
>index KGRIGG.ZIP_PLUS_4_ZIP_IND loaded successfully with 1664904 keys
>index KGRIGG.ZIP_PLUS_4_ADD_LOW_IND was made unusable due to:
>ORA-01652: unable to extend temp segment by 5394 in tablespace SYSTEM
>index KGRIGG.ZIP_PLUS_4_ZIP_ADD_HIGH_IND was made unusable due to:
>ORA-01652: unable to extend temp segment by 5394 in tablespace SYSTEM
>
>
>I am guessing that adding another large datafile to the SYSTEM tablespace
>will help this problem in the future for the temporary space it
>needs....however, what do I do about the indexes that were made unusuable?
>Do I need to rebuild them somehow...if so, how do I go about doing this?
>
>All advice and help greatly appreciated!!
>
>Kelly
>kgrigg_at_acxiom.com
>
>ps. Please cc by mail too...our newsfeed is not reliable here..
>
>-------------------==== Posted via Deja News ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet
Received on Thu Nov 27 1997 - 00:00:00 CST