Eric, thanks for your reply. I absolutely agree w/ your approach. Unfortunately the business requirement is data should be available for access in e.g. 30 minutes once they arrive. But we can't exchange partition that often -- how many partitions would the table have at the end of, say 3 months? Every time the 1502 shows up, an immediate retry of same query always succeeds - no index rebuild needed. Looks like there're some "holes" in the SINGLEROW option. I'm trying to understand the reason behind. Thanks! -Jessica -----Original Message----- From: Eric Buddelmeijer [mailto:Eric.Buddelmeijer@xxxxxxxxxx] Sent: Saturday, November 05, 2005 12:42 AM To: Jessica Mao; oracle-l@xxxxxxxxxxxxx Subject: RE: Index in unusable mode during direct load w/ singlerow Hi Jessica, I don't know much about sqlloader. I do know you could prevent this from happening by loading into a separate table, build the necessary indexes and then do a partition exchange from the 'work' table to the partitioned table. Nobody notices you are loading until you do the partition exchange and that is a 5 second operation as it involves only dictionary updates. Eric -----Oorspronkelijk bericht----- Van: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] Namens Jessica Mao Verzonden: vrijdag 4 november 2005 20:05 Aan: oracle-l@xxxxxxxxxxxxx Onderwerp: Index in unusable mode during direct load w/ singlerow Hi, We have a range partitioned table, there's sqlldr running in direct mode w/ SINGLEROW option on 1 of the partitions, meanwhile there's a procedure who queries the table using the partition key as the only predicate. Every once in a while, we get an ora-1502 "index '%s.%s' or partition of such index is in unusable state". I thought using SINGLEROW should avoid such error? The db is 9.2.0.6. happens on both hp and sun platform. Thanks! -Jessica -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l