This package contains a number of procedures that perform checks on the objects and identify the corrupted objects, and then it proceeds to repair those objects.

However, we must first discuss the procedure ADMIN_TABLES which creates a table in the SYS Schema that is used by the DBMS_REPAIR package to store its outputs.

The name of this table is prefixed with REPAIR.

DESC DBMS_REPAIR

As you can see, the DBMS_REPAIR package contains a number of procedures that perform checks on the corrupted objects and then store this information in the table created by the ADMIN_TABLES procedure found in the package itself, and then you can proceed to address those issues.

Now, we will create the table that the DBMS_REPAIR package will use to store the outputs; we will name this table REPAIR_CORRUPT_TABLE.

Note that it starts with the word REPAIR and will be created in the SYS Schema.

Begin

dbms_repair.admin_tables(

table_name=>'REPAIR_CORRUPT_TABLE',

table_type=>dbms_repair.repair_table,

action=>dbms_repair.create_action);

end; 

The table REPAIR_CORRUPT_TABLE has been created, and you can query it; note that it does not contain any data.

After performing checks on the objects using the DBMS_REPAIR package, you can query this table to find out the inspection information.

If any corrupted blocks are found on a specific table, for example, that information will be written in this table, along with a suggested solution for that problem.

Now, if we want to verify the integrity of the CORRUPT table located in the HR Schema using the DBMS_REPAIR package, we will use the CHECK_OBJECT procedure, which checks the table and then writes the result into the REPAIR_CORRUPT_TABLE.

If any corrupted blocks are found, you can perform a query on the REPAIR_CORRUPT_TABLE to confirm the integrity of the CORRUPT table; the data will be added to the REPAIR_CORRUPT_TABLE only if corrupted blocks are found in the table.

In case corrupted blocks are found, if one of the users queries this table, the query operation will fail due to the presence of corrupted blocks in the table.

However, the FIX_CORRUPT_BLOCKS procedure can be used to mark the corrupted blocks, and then these blocks can be ignored using the SKIP_CORRUPT_BLOCKS procedure.

Of course, this solution is costly, as it leads to the loss of some data due to ignoring the corrupted blocks, but in the end, it results in successful query operations on the table.