How data under V$DATABASE_BLOCK_CORRUPTION get refreshed

If there are any block corruption under oracle then an entry is get created under V$DATABASE_BLOCK_CORRUPTION. 

SQL> select count(*) from V$DATABASE_BLOCK_CORRUPTION;
    COUNT(*)------------         200
SQL>

Above sql shows that there are 200 block corruption in my database. I have discovered that few of those blocks are related to indexes. What I have done is dropped and created the index back. This way the index is created again and the corrupt blocks related to indexes are fixed.

After this if I run the count(*) query on V$DATABASE_BLOCK_CORRUPTION, I will still get the same results.

Reason:

V$DATABASE_BLOCK_CORRUPTION get updated when oracle reads a block and finds that there are corruption under that block. The block could remain corrupt for years if it was never queried.

So how to get the current picture and update V$DATABASE_BLOCK_CORRUPTION.

The easiest way is go thought and check the blocks and that can be done by RMAN with following command

RMAN> backup validate check logical database;

Above command will go though each and every block of the database and check if there is any corruption and update V$DATABASE_BLOCK_CORRUPTION, it will also remove previous entries which says there is a corruption in index and that was fixed. After running the validation when I again select from V$DATABASE_BLOCK_CORRUPTION blow were the results.

SQL> select count(*) from V$DATABASE_BLOCK_CORRUPTION;
    COUNT(*)------------         40SQL>