Tuesday, October 27, 2020
    Home Oracle Troubleshooting How to Detect Block Corruption in Oracle?

    How to Detect Block Corruption in Oracle?

    Block Corruption in Oracle

    oracle block corruption, block corruption, oracle table

    Analysis and Investigation of Corrupt Block in Oracle Database

    Oracle has four methods for detecting corruption. The analyze commend with the validate structure option can be used to verify the structure of a table, index, or cluster, and to confirm that a table and its indexes are in sync. The external DB_VERIFY utility (dbf in Unix/Linux) is used to validate blocks in data files that are offline. The init.ora parameter DB_BLOCK_CHECKING=true forces verification when a block is changed, and any errors detected are written to the alert.log.

    - Advertisement -

    The DBMS_REPAIR package gives you the ability to detect corrupt blocks in tables, partitions, and indexes. You can detect and report corrupt blocks to a repair table, make the object usable, and then repair the blocks and rebuild lost data. During the repair, data might be lost and logical inconsistencies might be introduced; therefore, it is important that you understand the significance of the data before making repair decisions.

    DBMS_REPAIR Package in Oracle

    The procedures in the DBMS_REPAIR package are used to discover, repair, or ignore corrupt blocks. These procedures are also used to rebuild freelists, report on orphaned indexes, and perform administrative tasks on corrupt blocks.


    Grants administrative privileges such as the ability to delete rows and drop objects. Use this procedure to create repair table.


    Detects corrupt blocks in an object, and reports the corruptions and fixes to the repair table.


    - Advertisement -

    Fixes corrupt blocks based on data gathered with CHECK_OBJECT.


    Indicates whether to ignore blocks marked corrupt during table or index scans, or to write an ORA-1578 error message to the alert.log when a marked corrupt block is read.


    Rebuilds the object’s freelists if they are corrupted.


    Lists the index keys that point to rows in corrupt data blocks.

    Next time we will explain about more of DBMS_REPAIR package and its limitations. During database services it is very essential to take care of corruption in 24/7 running large databases.

    Wish you all the best.

    - Advertisement -
    - Advertisment -

    Most Popular

    Five Reasons Why Database Outsourcing

    The blog post explains why database outsourcing and remote dba support becomes most popular during present time of Covid-19 Pandemic.

    Hardware of Database Server

    The blog post explains how to choose best hardware of your database server for getting high performance and security.

    oracle 19c new features – Automatic Indexing

    A major Oracle 19c autonomous database new Feature automatic indexing using DBMS_AUTO_INDEX some details and how to article.

    Differences Raw Device, ASM Device and CFS

    The article explains basic differences of ASM device, raw device and Oracle cluster file system cfs with fundamentals

    Recent Comments