Oracle Concepts - DUMP_ORPHAN_KEYS Oracle Tips by Burleson Consulting

DUMP_ORPHAN_KEYS

If the search with CHECK_TABLE turns up corrupt blocks, the DUMP_ORPHAN_KEYS procedure is used to retrieve key values from the table or index to facilitate the rebuild of the damaged block. Again, notice in table 7 how the positioning of the KEY_COUNT OUT attribute forces us to use positional nomenclature to use this procedure. The DUMP_ORPHAN_KEYS procedure has seven possible input variables where only the first two are required and one required output variable. Another problem is that if you have more than 32,767 problem entries this procedure will fail on numeric overflow since the output is specified as a BINARY_INTEGER instead of a plain old NUMBER.

Table 7: DUMP_ORPHAN_KEYS Procedure IN and OUT Parameters

An example run of this procedure is shown in Listing 9.

Listing 9 Example Run of the DUMP_ORPHAN_KEYS Procedure

SQL> execute dbms_repair.dump_orphan_keys( schema_name=>'GRAPHICS_DBA',object_name=>'PK_INTERNAL_GRAPHICS', key_count=>:x);

PL/SQL procedure successfully completed.

SQL> print x 

        X
---------
        0

Sorry I can't provide more exciting examples but I don't know of an easy way to generate corrupt blocks so I could demonstrate actual results.

FIX_CORRUPT_BLOCKS

The FIX_CORRUPT_BLOCKS procedure allows you to tell oracle to mark the blocks as software corrupt and thus skip them, or, leave them as is and generate errors. The procedure has six possible input variables two of which are required and one out parameter as shown in Table 8. Guess what, yep, the OUT parameter FIX_COUNT is once again placed so that we have to use positional nomenclature or specify values for all of the input parameters in order to use the procedure. Also, once again, the OUT parameter FIX_COUNT is a BINARY_INTEGER limiting the number of possible fixes to 32,767.

Table 8: FIX_CORRUPT_BLOCKS Procedure IN and OUT Parameters

An example execution of this procedure is shown in Listing 10.

Listing 10 Example Execution of the FIX_CORRUPT_BLOCKS Procedure

SQL> execute dbms_repair.fix_corrupt_blocks(schema_name=>'GRAPHICS_DBA',object_name=>'INTERNAL_GRAPHICS', fix_count=>:x);

PL/SQL procedure successfully completed.

SQL> print x 

        X
---------
        0

REBUILD_FREELISTS

The procedure REBUILD_FREELISTS is used to rebuild the freelists of tables that have been repaired to reflect the loss of the corrupt blocks. This procedure has four possible inputs, two of which have default values as shown in table 9. Amazingly enough Oracle put the defaults at the end of the variable list so positional naming isn't required, however, the developer who created all of these procedures has obviously never heard of the UPPER function so you must specify your arguments in upper case or an error will occur.

Table 9: REBUILD_FREELISTS Procedure IN Parameters

An example run of this procedure is shown in Listing 11.

Listing 11 Example Run of the REBUILD_FREELISTS Procedure

Let's try lower case and see how user friendly this procedure is:

SQL> execute dbms_repair.rebuild_freelists('graphics_dba','internal_graphics');

BEGIN dbms_repair.rebuild_freelists('graphics_dba','internal_graphics'); END;

*

ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_REPAIR", line 278
ORA-06512: at line 1

I guess the answer is not very. Let's do it again with upper case inputs:

SQL> execute dbms_repair.rebuild_freelists('GRAPHICS_DBA','INTERNAL_GRAPHICS');

PL/SQL procedure successfully completed.

If there is only one freelist group, the master freelist is updated with all free blocks and the other freelists are zeroed. If the object has multiple freelist groups then the master freelist in each freelist group is updated in a round-robin fashion and the rest of the freelists are zeroed. One question, since this procedure will be required to be executed after any run of the FIX_CORRUPT_BLOCKS procedure, why wasn't the functionality simply added to that procedure?

SKIP_CORRUPT_BLOCKS

The final procedure in the DBMS_REPAIR package is the SKIP_CORRUPT_BLOCKS procedure. The SKIP_CORRUPT_BLOCKS procedure is used to mark the corrupt blocks software corrupt and tell Oracle to skip those blocks during table and index scans. If the object specified is a cluster, it applies to all of the tables in the cluster and their respective indexes. The SKIP_CORRUPT_BLOCKS procedure has four possible inputs, two of which have default values as shown in Table 10. Amazingly enough Oracle put the defaults at the end of the variable list so positional naming isn't required, however, the developer who created all of these procedures has obviously never heard of the UPPER function so you must specify your arguments in upper case or an error will occur.

Table 10: SKIP_CORRUPT_BLOCKS Procedure IN Parameters

An example run of the SKIP_CORRUPT_BLOCKS procedure is shown in Listing 12.

Listing 12 Example Run of the SKIP_CORRUPT_BLOCKS procedure

SQL> execute dbms_repair.skip_corupt_blocks('GRAPHICS_DBA','INTERNAL_GRAPHICS');

PL/SQL procedure successfully completed.

This is an excerpt from the eBook "Oracle DBA made Simple".

For more details on Oracle database administration, see the "Easy Oracle Jumpstart" by Robert Freeman and Steve Karam.  It?s only $19.95 when you buy it directly from the publisher here.