请教索引分区失效的原因

14eer

请问除了对表MOVE TABLESPACE之外会造成一个索引unusable的原因有哪些，包括非分区表的问这个问题是因为一个表的索引失效 导致OGG复制进程在复制一条INSERT语句时，由于索引unusable,造成语句复制失败 进程ABEND 刚才仔细检查了一下 这个表不是分区表 和业务系统核对了他们说没做MOVE TABLESPACE操作 所以很奇怪

14eer

补充一下
数据库版本10.2.0.2.0
ogg软件版本11.1.1.0.2
OGG报错为
2013-08-05 07:41:53  WARNING OGG-00869  Oracle GoldenGate Delivery for Oracle, repea.prm:  OCI Error ORA-01502: index 'SAPSR3.AUFK~0' or partition of such in
dex is in unusable state (status = 1502), SQL <INSERT /*+ RESTRICT_ALL_REF_CONS */ INTO "SAPSR3"."AUFK" ("MANDT","AUFNR","AUART","AUTYP","REFNR","ERNAM","ERD
AT","AENAM","AEDAT","KTEXT","LTEXT","BUKRS","WERKS","GSBER","KOKRS","CCKEY","KOSTV","STORT>.
详细已经上传到附件了

Maclean Liu(刘相兵

ODM FINDING:

Six types of maintenance operations can mark index partitions INDEX UNUSABLE.
In all cases, you must rebuild the index partitions when the operation is
complete.

  1)    Operations like Import Partition or conventional path SQL*Loader
        that offer an option to bypass local index maintenance.  When the
        Import is complete, the affected local index partitions are marked
        IU.

  2)    Direct path SQL*Loader leaves affected local index partitions and
        global indexes in an IU state if the index is out of date with
        respect to the data that it indexes.  The index can be out of date
        for the following reasons:

        a)  The index was not maintained during the load due to a space
            management error (for example, out of extents ORA-1653 or
            ORA-1652).

        b)  The user requested the SKIP_INDEX_MAINTENANCE clause.

  3)    Partition maintenance operations like ALTER TABLE MOVE PARTITION
        that change rowids.  These operations mark the affected local index
        partition and all global index partitions IU.

  4)    Partition maintenance operations like ALTER TABLE TRUNCATE PARTITION
        or DROP PARTITION that remove rows from the table.  These operations
        mark the affected local index partition and all global index
        partitions IU.

  5)    Partition maintenance operations like ALTER TABLE SPLIT PARTITION
        that modify the partition definition of local indexes but do not
        automatically rebuild the index data to match the new definitions.
        These operations mark the affected local index partitions IU.  ALTER
        TABLE SPLIT PARTITION also marks all global index partitions IU
        because it results in changes to rowids.

  6)    Index maintenance operations like ALTER INDEX SPLIT PARTITION that
        modify the partitioning definition of the index but do not
        automatically rebuild the affected partitions.  These operations
        mark the affected index partitions IU.  However, if you split a
        USABLE partition of a global index, resulting partitions are created
        USABLE.  If the partition that was split was marked IU, then so are
        the partitions resulting from the split.  Note that dropping a
        partition of a global index that is either IU or is not empty causes
        the next partition of the index to become IU.


The ALTER INDEX REBUILD PARTITION statement can be used to regenerate a single
partition in a local or global partitioned index.  This saves you from having
to perform DROP INDEX and then CREATE INDEX, which would affect all partitions
in the index.

14eer

谢谢刘大！ 非常给力

14eer

Maclean Liu(刘相兵 发表于 2013-8-5 15:41
ODM FINDING:

Six types of maintenance operations can mark index partitions INDEX UNUSABLE.

那么非分区索引有哪些情况会造成索引失效呢？

Maclean Liu(刘相兵

Indexes can become invalid or unusable whenever a DBA tasks shifts the ROWID values, thereby requiring an index rebuild. These DBA tasks that shift table ROWID's include:

Table partition maintenance - Alter commands (move, split or truncate partition) will shift ROWID's, making the index invalid and unusable.

CTAS maintenance - Table reorganization with "alter table move" or an online table reorganization (using the dbms_redefinition package) will shift ROWIDs, creating unusable indexes.

Oracle imports - An Oracle import (imp utility) with the skip_unusable_indexes=y parameter

SQL*Loader (sqlldr utility) - Using direct path loads (e.g. skip_index_maintenance) will cause invalid and unusable indexes.