There are many ways to detect the block corruption in a database. One of them is to use “ANALYZE TABLE” command as follows:
SQL> ANALYZE TABLE table_name VALIDATE STRUCTURE;
This command validates the integrity of the structure of the table being analyzed. To be more precise it validates the integrity of the db blocks of the underlying table.
This command is either successful or not successful. If not successful the error ORA-01499 is thrown.
If CASCADE is specified, the command also checks its indexes.
SQL> ANALYZE TABLE table_name VALIDATE STRUCTURE CASCADE;
3 responses so far ↓
1 p.srikanth // Dec 27, 2007 at 6:34 pm
ok fine will u plz explain wht happens when error ORA-01499 is thrown. next wht steps should i take to see the blk corruption.
2 Bill MacHose // Feb 5, 2008 at 10:05 pm
I’ve been getting errors every couple of weeks on an Oracle8i table. My fix is to drop and rebuild all the indexes on the table. I’m still investigating, but it appears to be the 6th of 6 indexes that is causing me trouble. Looking at the trace file was no help - I don’t know trace files so I didn’t know what I was looking for.
3 rajs // Feb 5, 2008 at 10:19 pm
Hi Bill,
Can you paste here what exactly the error is showing.
like something like as follows:
ORA-01578: ORACLE data block corrupted (file # 7, block # 13998)
ORA-01110: data file 22: ‘/oraclehome/oradata/V816/oradata/dcs/users01.dbf’
Regards,
Rajender
Leave a Comment