WHERE Clause Fails with ORA-01578(Data block corrupted)

Many of the DBAs face this problem day by day is Oracle Data Block Corrupted. Here I like to give a demonstration to solve this problem where index type segment is corrupted.

Symptom of the Problem:Queries with where clause against a table fail but queries without where clause run without error.

Cause of the Problem: This problem is caused due to a corrupted block in one or more blocks belonging to an index type of segment. Since the corrupted block does not belong to the table, some statements could be executed successfully if the information is retrieved via full tablescan.

How to Fix:
(A) Find out the objects where the corrupted block belongs:

SELECT tablespace_name, segment_type, owner, segment_name FROM dba_extents WHERE file_id = and between block_id AND block_id + blocks - 1 ;

For detail result, I used to use dbverify utility for details about the corrupted data file.

(B) Find out which table the INDEX is on:

SELECT table_owner, table_name FROM dba_indexes WHERE owner=’’ AND index_name=’’;

(C) Determine if the index supports a CONSTRAINT:

SELECT owner, constraint_name, constraint_type, table_name FROM dba_constraints WHERE owner=’’ AND constraint_name=’’ ;

Possible values for CONSTRAINT_TYPE are:

P The index supports a primary key constraint.

U The index supports a unique constraint.

(D) Check if it is type P:

If the INDEX supports a PRIMARY KEY constraint (type “P”) then check if the primary key is referenced by any foreign key constraints: Eg:

SELECT owner, constraint_name, constraint_type, table_name FROM dba_constraints WHERE r_owner=’’ AND r_constraint_name=’’ ;

Solution to the problem:

1)Recreate the index. It will require to delete all constraints supported or referenced by the index before.

2)Database recover when the size of the index is too big to be recreated and the size of the table involved does not enable the application to access the information without impacting the performance of the application.