Oracle and DB2, Comparison and Compatibility/Process Model/Consistency/Oracle

Oracle

The ANALYZE command, seen earlier in this chapter in the optimizer section, is the command that Oracle uses to verify the integrity of a table, index, cluster, or materialized view. In this instance, you use the ANALYZE statement with the VALIDATE STRUCTURE option. If the structure is corrupt you will get an error message.

Validation of an index, for example confirms that every entry in the index points to the correct row of the associated table. To correct a corrupted index, table or cluster, you can drop and re-create it. If a materialized view is corrupt, you should do a refresh and see if this corrects the problem. If not, you need to drop and re-create it.

You can validate an object and all dependent objects with the CASCADE option. Because CASCADE is resource intensive, you can validate quicker with FAST. FAST checks for corruptions using an optimized check algorithm. FAST does not report details about the corruption, so if you find a corruption, you need to use CASCADE option without FAST to locate it. It is also possible to validate online in your SQL, but there is a performance overhead.

dbverify allows external analysis of database blocks to see if the actual block structure corresponds to the expected block structure. dbverify may report phantom corruptions if it is run against an open database (The DBWR process may interfere with dbverify).

Another means of checking for corruption in your Oracle database is to export it (either the whole database, or selected objects). If you are only interested in corruptions, you can export the database to a dummy file. Errors found in the export will indicate if there are corruptions/consistency problems in the database.