Why do you have to make the indexes unusable? Can't you do a direct load into the table (nologging mode)?
Waleed -----Original Message----- To: Multiple recipients of list ORACLE-L Sent: 10/14/02 7:08 PM Thats what we exactly did except that iniformatica starts a separate session when it is loading the target which results in the error "unusable state" . sathish On Mon, 14 Oct 2002 15:44:02 -0800, "Khedr, Waleed" <[EMAIL PROTECTED]> said: > In Informatica there is a stored procedure transform (runs any Oracle SP) > that you can make it "pre source load". > So the SP would be called before loading the table. > > Regards, > > Waleed > > -----Original Message----- > Sent: Monday, October 14, 2002 7:04 PM > To: Multiple recipients of list ORACLE-L > > > Hello All, > This problem pertains to local bitmap index in dw env. > > We are using informatica to load our fact tables. > > i have a fact table partitioned on period key (range partition). > We have built local bitmap indexes on the foreign keys. > During the loading of the fact table for month 1, we are making the local > bitmap index partition pertaining to month 1 > as UNUSABLE (through stored procedure being called at just before the > target is being loaded). In that same procedure > we also set ALTER SESSION SET SKIP_UNUSABLE_INDEXES = TRUE. > Once informatica starts to load the target, it opens up a new session > which in turn invalidates the alter session > and hence oracle terminates with an error saying "0ra-1052 index in > unusable state" > Please let me know if there is any workaround to this. > Is droping and recreating the bitmap index the ONLY option ???. this fact > table is going to grow to about 150 mill rows in > 12 month time frame , avg row length being 100 bytes) > > > oracle ver 9.2., > > > TIA, > > sathish. > > -- > http://fastmail.fm - The way an email service should be > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting services > --------------------------------------------------------------------- > 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: Khedr, Waleed > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting services > --------------------------------------------------------------------- > 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). > -- http://fastmail.fm - Access all of your messages and folders wherever you are -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- 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: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- 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).