Re: table fetch continued row

From: Jusung Yang <JusungYang_at_yahoo.com>
Date: 18 Oct 2002 00:34:41 -0700
Message-ID: <130ba93a.0210172334.707e99ee@posting.google.com>

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.

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.

• Jusung Yang

Frederic Payant <> wrote in message news:<dt2uqug7r51sr4l5k63ror20m9t2nf2njd_at_4ax.com>...
> Hi,
> With Oracle 8.1.7 on Solaris 8 :
>
> On a partitioned table we had to create a global unique index .
> At each time we create new partitions (ie every night) we have
> to rebuild this index.
> OK
> We had last night an error :
> ORA-01502: index 'PIN.X_EVENT_ACT_CONTENT_AUTH_I1' or
> partition of such index is in unusable state
> "insert into x_event_act_content_auth_t ( bla bla ... )"
> It was at the exact time where we rebuild indexes.
>
> What's strange is that we rebuild indexes using the ONLINE
> clause :
> "ALTER INDEX X_EVENT_ACT_CONTENT_AUTH_I1 REBUILD ONLINE
> NOLOGGING"
>
> If I read Oracle doc I find :
> ONLINE :
> Specify ONLINE to allow DML operations on the table or
> partition during rebuilding of the index.
> Restrictions:
> 1)You cannot specify ONLINE when rebuilding the secondary
> index of an index-organized table.
> 2)Parallel DML is not supported during online index building.
> If you specify ONLINE and then issue parallel DML
> statements, Oracle returns an error.
>
> My table is not an index-organized table, and is created with
> no parallel options.
>
> Is there anything I misunderstood ??
>
> Thank for your help and don't hesitate to ask me any further
> information
>
>
> Amicalement
> Frédéric PAYANT
Received on Fri Oct 18 2002 - 02:34:41 CDT