1. Get the list of corrupted blocks grep 'ORA-01578' alert_DB.log | sort -u > tmp.txt
2. Generate SQL script to find segments with corrupted blocks cat tmp.txt | awk '{ printf("%s %s\n", $8, $11); }' | \ awk -F\, '{ printf ("%s %s\n", $1, $2); }' | \ awk -F\) '{ printf("%s\n", $1); }' | \ awk '{printf ("SELECT SEGMENT_NAME, SEGMENT_TYPE, RELATIVE_FNO FROM DBA_EXTENTS WHERE FILE_ID = %s AND %s BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;\n", $1, $2); }' > find_segs.sql 3. Run SQL to find out which objects have corrupted blocks SQLPLUS> col SEGMENT_NAME format a30 SQLPLUS> spool corrupted_segments.log SQLPLUS> @find_segs SQLPLUS> spool off
4. Sort the output to remove duplicates sort -u corrupted_segments.log
