Problem Key 'ORA 1578' was completely flood controlled (0x2)

 Problem Key 'ORA 1578' was completely flood controlled (0x2)

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

Options to identify the Corruption

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

Using SQL

SELECT FILE#, 

       BLOCK#, 

       BLOCKS, 

       TO_CHAR(CORRUPTION_CHANGE#) CORRUPTION_CHANGE#, 

       CORRUPTION_TYPE 

  FROM V$DATABASE_BLOCK_CORRUPTION;

SQL> SELECT FILE#,

       BLOCK#,

       BLOCKS,

       TO_CHAR(CORRUPTION_CHANGE#) CORRUPTION_CHANGE#,

       CORRUPTION_TYPE

  FROM V$DATABASE_BLOCK_CORRUPTION;  2    3    4    5    6

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE#

---------- ---------- ---------- ----------------------------------------

CORRUPTIO

---------

       325     196462          1 33745505699576

CORRUPT

SQL>

SELECT e.owner, 

       e.segment_type, 

       e.segment_name, 

       e.partition_name, 

       c.file#,

       greatest(e.block_id, c.block#) corr_start_block#,

       least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#,

       least(e.block_id+e.blocks-1, c.block#+c.blocks-1),

       corruption_type description

  FROM DBA_EXTENTS e, 

       V$DATABASE_BLOCK_CORRUPTION c

 WHERE e.file_id = c.file#

   AND e.block_id <= c.block# + c.blocks - 1

   AND e.block_id + e.blocks - 1 >= c.block#

SQL> SELECT e.owner,

       e.segment_type,

       e.segment_name,

       e.partition_name,

       c.file#,

       greatest(e.block_id, c.block#) corr_start_block#,

       least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#,

       least(e.block_id+e.blocks-1, c.block#+c.blocks-1),

       corruption_type description

  FROM DBA_EXTENTS e,

       V$DATABASE_BLOCK_CORRUPTION c

 WHERE e.file_id = c.file#

   AND e.block_id <= c.block# + c.blocks - 1

   AND e.block_id + e.blocks - 1 >= c.block#  2    3    4    5    6    7    8    9   10   11   12   13   14  ;

OWNER                                                                                                                            SEGMENT_TYPE       SEGMENT_NAME

-------------------------------------------------------------------------------------------------------------------------------- ------------------ --------------------------------------------------------------------------------------------------------------------------------

PARTITION_NAME                                                                                                                        FILE# CORR_START_BLOCK# CORR_END_BLOCK# LEAST(E.BLOCK_ID+E.BLOCKS-1,C.BLOCK#+C.BLOCKS-1) DESCRIPTI

-------------------------------------------------------------------------------------------------------------------------------- ---------- ----------------- --------------- ------------------------------------------------ ---------

IBANKING                                                                                                                         INDEX              IDX_DT_ACT_STATEMENT

                                                                                                                                        325            196462          196462                                               196462 CORRUPT

SELECT s.owner, 

       s.segment_type, 

       s.segment_name, 

       s.partition_name, 

       c.file#,

       header_block corr_start_block#,

       header_block corr_end_block#,

       1 blocks_corrupted,

       corruption_type||' Segment Header' description

  FROM DBA_SEGMENTS S, 

       V$DATABASE_BLOCK_CORRUPTION C

 WHERE s.header_file = c.file#

   AND s.header_block between c.block# and c.block# + c.blocks - 1

SELECT null owner, 

       null segment_type, 

       null segment_name, 

       null partition_name, 

       c.file#,

       greatest(f.block_id, c.block#) corr_start_block#,

       least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#,

       least(f.block_id+f.blocks-1, c.block#+c.blocks-1)

 - greatest(f.block_id, c.block#) + 1 blocks_corrupted,

       'Free Block' description

  FROM DBA_FREE_SPACE F, 

       V$DATABASE_BLOCK_CORRUPTION C

 WHERE f.file_id = c.file#

   AND f.block_id <= c.block# + c.blocks - 1

   AND f.block_id + f.blocks - 1 >= c.block#

ORDER BY file#, corr_start_block#;

Solution:

Options to fix Corruption

Rebuild Index Online

SQL> ALTER INDEX IBANKING.IDX_DT_ACT_STATEMENT REBUILD ONLINE;

Index altered.

SQL>