Index rebuild failed: ORA-08104:

[Former Member]

Hi,

while rebuliding index using RSANAOR it was taking more time, so I have canceled it.

when again trying to rebuild using BRTools for same index, it is giving below error:

Options for rebuild of indexes: SAPR3P.ZAPO_ORDERS~0 (degree 1)

1 ~ New destination tablespace (newts) ........ []

2 - Parallel threads (parallel) ............... [1]

3 ~ Index parallel degree (degree) ............ []

4 ~ Category of initial extent size (initial) . []

5 - Index rebuild mode (mode) ................. [online]

6 - SQL command (command) ..................... [alter index "SAPR3P"."ZAPO_ORDERS~0" rebuild online]

Standard keys: c - cont, b - back, s - stop, r - refr, h - help

-

---

BR0662I Enter your choice:

c

BR0280I BRSPACE time stamp: 2009-03-15 18.14.01

BR0663I Your choice: 'c'

BR0259I Program execution will be continued...

BR0280I BRSPACE time stamp: 2009-03-15 18.14.01

BR1151I Starting online index rebuilding...

BR0280I BRSPACE time stamp: 2009-03-15 18.14.01

BR0301E SQL error -8104 at location BrSqlExecute-1, SQL statement:

'/* BRSPACE */ alter index "SAPR3P"."ZAPO_ORDERS~0" rebuild online'

ORA-08104: this index object 113478 is being online built or rebuilt

BR1017E Execution of SQL statement 'alter index "SAPR3P"."ZAPO_ORDERS~0" rebuild online' failed

BR0669I Cannot continue due to previous warnings or errors - you can go back to repeat the last action

BR0280I BRSPACE time stamp: 2009-03-15 18.14.01

BR0671I Enter 'b[ack]' to go back, 's[top]' to abort:

============================================

I have gone thro' Note 682926 :

the script;

set serveroutput on

begin

if dbms_repair.online_index_clean() then

dbms_output.put_line('All fragments cleaned');

else

dbms_output.put_line('DML Locks on some tables.');

dbms_output.put_line('Not all fragments cleaned. ');

dbms_output.put_line('Try again later');

end if;

end;

/

kindly suggest for necessary action.

can i run the script on prodution server without taking in mount state.

Regards,

Gokul Chitode

[Former Member · Accepted solution]

Hi Gokul,

Refer SAP note 682926.

Regards,

Sachin Rane.

[stefan_koehler]

Hello Gokul,

yes you have to run this script in the actual state.

Metalinknote #375856.1 has more background informations about that topic.

The dbms_repair.online_index_clean function has been created to cleanup online index rebuilds. Use the dbms_repair.online_index_clean function to resolve the issue.
Please note if you are unable to run the dbms_repair.online_index_clean function it is due to the fact that you have not installed the patch for Bug 3805539 or are not running on a release that includes this fix.  The fix for this bug is a new function in the dbms_repair package called dbms_repair.online_index_clean, which has been created to cleanup online index [[sub]partition] [re]builds. New functionality is not allowed in patchsets; therefore, this is not available in a patchset but is available in 10gR2.

Regards

Stefan