Re: Index marked unuseable
Date: Mon, 27 Apr 2009 15:50:40 -0400
Message-ID: <76b3d4e30904271250q7f2d32e2s9e63f8d9d0b49a92_at_mail.gmail.com>
Thanks Jared, you had the right idea. I had a Q&A session with ETL folks and found the code that runs before ETL when they mark it as unusable and then rebuild it later. Thanks for the help!
On Mon, Apr 27, 2009 at 12:15 PM, Jared Still <jkstill_at_gmail.com> wrote:
> Saad,
>
> It is quite possible that the ETL process is doing 'alter index INDEXNAME
> unusable'
> prior to the ETL data loads.
>
> As the index is UNUSABLE, it won't be updated during the load.
>
> Following the load, all that is needed is 'ALTER INDEX REBUILD' to make the
>
> index usable again.
>
> This seems rather clever to me, provided there are no unwanted side
> effects.
>
> The alternative would be drop index/create index, which would require
> having
> all current DML for index creation.
>
> The index unusable/rebuild scheme avoids having to keep track of how the
> index was built - just issue rebuild.
>
> HTH
>
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist
>
>
>
> On Mon, Apr 20, 2009 at 12:24 PM, Saad Khan <saad4u_at_gmail.com> wrote:
>
>> Hi Guyz,
>>
>> I've Datamart running in a new database which just went live yesterday. We
>> have ETL running for it. Since last few days, I've been seeing TONS of
>> following messages in the alert file during the time whenever ETL is run.
>>
>> The most wierd thing is that, when I checked user_indexes and
>> user_ind_subpartitions, the status column shows every index as USABLE. This
>> is puzzling.
>> Does anyone have any idea why such messages are being generated and if
>> these are legitimate messages, why the indexes are still usable?
>>
>> Messages in logfile:
>> *Index<username>.<indexname> or some [sub]partitions of the index have
>> been marked unusable
>> Mon Apr 20 11:42:43 2009
>> Index<username>.<indexname> or some [sub]partitions of the index have been
>> marked unusable
>> Mon Apr 20 11:42:43 2009
>> Index<username>.<indexname> or some [sub]partitions of the index have been
>> marked unusable
>> Mon Apr 20 11:42:43 2009
>> Index<username>.<indexname> or some [sub]partitions of the index have been
>> marked unusable*
>>
>>
>>
>> Thanks,
>> Saad
>>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Apr 27 2009 - 14:50:40 CDT