Tuesday, June 24, 2008

ORA-08102: index key not found, obj# 10837, file 23, block 5548 (2)

SQL> alter table "OWNER"."XXX" modify partition "XXX_PART02" shrink space;

ORA-08102: index key not found, obj# 10837, file 23, block 5548 (2)

ORA-08102: index key not found, obj# string, file string, block string (string)
Cause: Internal error: possible inconsistency in index
Action: Send trace file to your customer support representative, along with information on reproducing the error


SQL> select tablespace_name from dba_data_files where file_id=23;

TABLESPACE_NAME
------------------------------
XXX_INDX02

SQL> select index_name, partition_name from dba_ind_partitions where tablespace_name='XXX_INDX02';

INDEX_NAME PARTITION_NAME
------------------------------ ------------------------------
XXX_INDX03 XXX_INDX01_PART02
XXX_INDX01 XXX_INDX01_PART02
XXX_INDX02 XXX_INDX01_PART02


So, I rebuilt every indexes on XXX_INDX02 tablespace:

SQL> ALTER INDEX "OWNER"."XXX_INDX01" REBUILD PARTITION "XXX_INDX01_PART02" online;

After rebuild indexes, I can shrink tablespace....no problem:

SQL> alter table "OWNER"."XXX" modify partition "XXX_PART02" shrink space;

=> Actually I didn't need to rebuild every INDEXES:

SQL> select object_name, object_type from dba_objects where object_id=10837;

OBJECT_NAME OBJECT_TYPE
----------------- ----------------
XXX_INDX02 INDEX PARTITION

SQL> select partition_name from dba_ind_partitions where tablespace_name='XXX_INDX02' and index_name='XXX_INDX02';

PARTITION_NAME
------------------------------
XXX_INDX01_PART02

So,

SQL> ALTER INDEX "OWNER"."XXX_INDX02" REBUILD PARTITION "XXX_INDX01_PART02" online;


Alter only one index.



Enjoy...

No comments: