Q: rebuilding index partitions

[vince_laurent]

I got this error on one of our BW systems:

BR0986W Partitioned index SAPR3./BIC/B0000550001~0 is unbalanced - please rebuild the index partitionsSQL> select partition_name from USER_IND_PARTITIONS

I tried a rebuild online but got an ORA-14086

$ oerr ora 14086

14086, 00000, "a partitioned index may not be rebuilt as a whole"

// *Cause: User attempted to rebuild a partitioned index using

// ALTER INDEX REBUILD statement, which is illegal

// *Action: Rebuild the index a partition at a time (using

// ALTER INDEX REBUILD PARTITION) or drop and recreate the

// entire index

So I tried locating the name:

SQL> select partition_name from USER_IND_PARTITIONS

2 where index_name = '/BIC/B0000550001~0';

PARTITION_NAME

-

---

/BIC/B00005500010000000002

Then rerunning the command:

SQL> alter index '/BIC/B00005500010000000002' rebuild partition '/BIC/B0000550001~0' online;

alter index '/BIC/B00005500010000000002' rebuild partition '/BIC/B0000550001~0' online

*

ERROR at line 1:

ORA-00953: missing or invalid index name

What am I missing/typing wrong?

[former_member185954 · Accepted solution]

Hi,

You should do this..

alter index "SAPR3"."/BIC/B0000550001~0" rebuild partition "/BIC/B00005500010000000002" online;

Regards,

Siddhesh

[vince_laurent · Accepted solution]

SQL> alter index "SAPR3"."/BIC/B0000550001~0" rebuild partition "/BIC/B00005500010000000002" online;

Index altered.

Thanks a ton!

Vince

p.s. I'd send you a beer if I could figure out how to!

[former_member185954 · Accepted solution]

HA HA... glad i could help..you can have my share of beer yourself and enjoy mate !

[Former Member]

Vince,

Rebuilding a simple index example:ALTER INDEX index_name REBUILD;

Rebuilding a partition example:

The following statement rebuilds partition PP11 in index PP.The rebuilding of the index partition will not be logged:

ALTER INDEX PP REBUILD PARTITION PP11 NOLOGGING;

ALTER INDEX [schema.]index REBUILD [online] [parameters (paramstring)];

In some cases, when dealing with index corruption, it may not be possible to rebuild the index as the corruption may still be present after the rebuild. The solution is to drop and recreate the index.

Regards

Vinod

[vince_laurent]

> The following statement rebuilds partition PP11 in

> index PP.The rebuilding of the index partition will not be logged:

> ALTER INDEX PP REBUILD PARTITION PP11 NOLOGGING;

> ALTER INDEX [schema.]index REBUILD [online]

> [parameters (paramstring)];

Thanks for the reply. The issue is I am unsure what index that index partition belongs too. When I use DB02 to find associated items I get:

Object Type

/BIC/B00005500010000000002 TABLE PART

/BIC/B00005500010000000002 INDEX PART

How can I tell what index this index partition belongs to?

Thanks!

[former_member185954]

Hi vince,

replace the single quotes with double quotes and the query would be fine.

Also remember to use Schema name.

Use something like "SAPR3"."/BIC/B0000550001~0"

Regards,

siddhesh

Message was edited by:

Siddhesh Ghag

[vince_laurent]

The double-quote thing worked... now I have to figure out what else went wrong!!

SQL> alter index "/BIC/B00005500010000000002" rebuild prtition "/BIC/B0000550001~0" online;

alter index "/BIC/B00005500010000000002" rebuild partition "/BIC/B0000550001~0" online

*

ERROR at line 1:

ORA-01418: specified index does not exist

SQL> alter index "SAPR3"."/BIC/B00005500010000000002" rebuild partition "SAPR3"."/BIC/B000

0550001~0" online;

alter index "SAPR3"."/BIC/B00005500010000000002" rebuild partition "SAPR3"."/BIC/B00005500

01~0" online

*

ERROR at line 1:

ORA-14010: this physical attribute may not be specified for an index partition

SQL> alter index "SAPR3"."/BIC/B00005500010000000002" rebuild partition online;

alter index "SAPR3"."/BIC/B00005500010000000002" rebuild partition online

*

ERROR at line 1:

ORA-14006: invalid partition name

[former_member185954]

Hi,

Please check the following notes:

Note 682926 - Composite SAP note: Problems with 'alter index rebuild'

Note 723149 - Error due to incorrect SQL syntax

Regards,

Siddhesh

[vince_laurent]

Thanks... read them. Didn't really help though...