Well, avoid accessing the table while the global index is being
rebuilt. Is local index a possibility for you? Is this a data
warehouse type of environment? The fact that a new partition is being
added daily to the table seems to suggest that it is. If so, why are
there insert statement run against the table? Seems a bit unusual.
Frederic Payant <> wrote in message news:<u926rucobvbdmv07v8svsc68o7no6mkdrc_at_4ax.com>...
> JusungYang_at_yahoo.com (Jusung Yang) :
>
> >Just curious. Why did you use that subject line? I don't know quite
> >know what it means. If you did not get too many responses, that might
> >be one of the reasons.
> Thanks for answering yourself. Title is a mistake (bad copy paste and
> no re-read)
>
> >
> >Depends on how the new partition is created, I think this is what
> >happened:
> >1. A new partition was created in table x_event_act_content_auth_t
> >2. Global index X_EVENT_ACT_CONTENT_AUTH_I1 became unsuable
> >3. Index was rebuilt with
> >ALTER INDEX X_EVENT_ACT_CONTENT_AUTH_I1 REBUILD ONLINE NOLOGGING
> >4. insert into x_event_act_content_auth_t was issued before 3 was
> >completed.
> >
> >The fact that the "ONLINE" clause was used in step 3 really does not
> >matter, since the index was "unusable" when the command was issued.
> >Until step 3 is completed and the status of the index becomes VALID,
> >any DML that accesses the global index will fail with ORA-01502.
> >
> You're surely right. Do you know any solution (migrating to 9i is not
> possible) to avoid this error - I mean to avoid Oracle to try to
> create this index during or before rebuilding ?
>
> Have a nice week
> Regards
> Frederic PAYANT - junior DBA ;-)
Received on Mon Oct 21 2002 - 01:24:46 CDT
