Find segments with corrupted block from alert log with ORA-01578

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