RE: Index in unusable mode during direct load w/ singlerow

  • From: "Jessica Mao" <jmao@xxxxxxxxxx>
  • To: "Eric Buddelmeijer" <Eric.Buddelmeijer@xxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 8 Nov 2005 12:28:59 -0800

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.


-----Oorspronkelijk bericht-----
Van: 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


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 happens on both hp and sun platform.

Thanks! -Jessica


Other related posts: