DBMS_REPAIR tips Oracle Tips by Burleson Consulting

DBMS_REPAIR Procedures

The DBMS_REPAIR package contains six procedures (as of this writing) that are usable callable and no user callable functions.

For additional information on handling corruption, see the BC expert notes on corruption and see MOSC Note 1088018.1 - Handling Oracle Database Corruption Issues.

• Using dbms_repair to Find Corrupt Blocks

• Repairing corrupt blocks using dbms_repair

• Tips on Oracle dbms_repair utility

• Description of the DBMS_REPAIR package

• Configuring the environment for the dbms_repair utility

Table 4: Procedures in DBMS_REPAIR

The following sections describe each procedure and give an example of its use.

ADMIN_TABLES

The ADMIN_TABLES procedure has the following input variables and no output variables as shown in table 5:

Table 5: ADMIN_TABLES Input Variables

The procedure is used to create, purge and drop the REPAIR_TABLE and ORPHAN_KEY_TABLE which are used during the repair of database tables and indexes. If the TABLE_TYPE is set to  REPAIR_TABLE then the GENERATE_DEFAULT_TABLE_NAME setting tells the procedure to set the table name to REPAIR_TABLE if the TABLE_TYPE is set to ORPHAN_TABLE then the TABLE_NAME is set to ORPHAN_KEY_TABLE by the procedure if the GENERATE_DEFAULT_TABLE_NAME value is entered. This procedure is a good example why defaulted values should be placed at the end of your argument list. Since the TABLE_NAME attribute is first it means that to use the default for it you must specify the positional naming for all other parameters in order to use the default value, take a look at Listing 7 to see what I mean.

Listing 7 Example Use of  DBMS_REPAIR.ADMIN_TABLES procedure.

First, let's build a repair table. Note that the name specified for the table is upper case, this is required, if you specify the name in lower case you will get an error on exception 24129.

SQL> execute dbms_repair.admin_tables( 'REPAIR_TABLE',dbms_repair.repair_table,dbms_repair.create_action); 

PL/SQL procedure successfully completed. 

SQL> desc repair_table

 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ---------------
 OBJECT_ID                                             NOT NULL NUMBER
 TABLESPACE_ID                                         NOT NULL NUMBER
 RELATIVE_FILE_ID                                      NOT NULL NUMBER
 BLOCK_ID                                              NOT NULL NUMBER
 CORRUPT_TYPE                                          NOT NULL NUMBER
 SCHEMA_NAME                                           NOT NULL VARCHAR2(30)
 OBJECT_NAME                                           NOT NULL VARCHAR2(30)
 BASEOBJECT_NAME                                                VARCHAR2(30)
 PARTITION_NAME                                                 VARCHAR2(30)
 CORRUPT_DESCRIPTION                                            VARCHAR2(2000)
 REPAIR_DESCRIPTION                                             VARCHAR2(200)
 MARKED_CORRUPT                                        NOT NULL VARCHAR2(10)
 CHECK_TIMESTAMP                                       NOT NULL DATE
 FIX_TIMESTAMP                                                  DATE
 REFORMAT_TIMESTAMP                                             DATE
 

Now let's create an orphan key table. The same admonishment about use of upper case applies.

SQL> execute dbms_repair.admin_tables('ORPHAN_KEY_TABLE',dbms_repair.orphan_table,dbms_repair.create_action);

PL/SQL procedure successfully completed. 

SQL> desc orphan_key_table

 Name                                                  Null?    Type
----------------------------------------------------- -------- ---------------------------------
 SCHEMA_NAME                                           NOT NULL VARCHAR2(30)
 INDEX_NAME                                            NOT NULL VARCHAR2(30)
 IPART_NAME                                                     VARCHAR2(30)
 INDEX_ID                                              NOT NULL NUMBER
 TABLE_NAME                                            NOT NULL VARCHAR2(30)
 PART_NAME                                                      VARCHAR2(30)
 TABLE_ID                                              NOT NULL NUMBER
 KEYROWID                                              NOT NULL ROWID
 KEY                                                   NOT NULL ROWID
 DUMP_TIMESTAMP                                        NOT NULL DATE
 
       Here is an example of using the 'GENERATE_DEFAULT_TABLE_NAME default value.

SQL> execute dbms_repair.admin_tables('GENERATE_DEFAULT_TABLE_NAME',dbms_repair.orphan_table, dbms_repair.create_action);

PL/SQL procedure successfully completed.

SQL> desc orphan_key_table;

 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ---------------
 SCHEMA_NAME                                           NOT NULL VARCHAR2(30)
 INDEX_NAME                                            NOT NULL VARCHAR2(30)
 IPART_NAME                                                     VARCHAR2(30)
 INDEX_ID                                              NOT NULL NUMBER
 TABLE_NAME                                            NOT NULL VARCHAR2(30)
 PART_NAME                                                      VARCHAR2(30)
 TABLE_ID                                              NOT NULL NUMBER
 KEYROWID                                              NOT NULL ROWID
 KEY                                                   NOT NULL ROWID
 DUMP_TIMESTAMP                                        NOT NULL DATE
 

And here is an example using the default value, which requires the positional specification type procedure call. If the defaults would have been placed after the required fields, this could have been avoided.

SQL> execute dbms_repair.admin_tables(
table_type=>dbms_repair.orphan_table,action=>dbms_repair.drop_action);

PL/SQL procedure successfully completed.

SQL> execute dbms_repair.admin_tables( table_type=>dbms_repair.orphan_table,action=>dbms_repair.create_action);

PL/SQL procedure successfully completed.

SQL> desc orphan_key_table

 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ---------------
 SCHEMA_NAME                                           NOT NULL VARCHAR2(30)
 INDEX_NAME                                            NOT NULL VARCHAR2(30)
 IPART_NAME                                                     VARCHAR2(30)
 INDEX_ID                                              NOT NULL NUMBER
 TABLE_NAME                                            NOT NULL VARCHAR2(30)
 PART_NAME                                                      VARCHAR2(30)
 TABLE_ID                                              NOT NULL NUMBER
 KEYROWID                                              NOT NULL ROWID
 KEY                                                   NOT NULL ROWID
 DUMP_TIMESTAMP                                        NOT NULL DATE
 

The other actions such as purge, are accessed the same way as is demonstrated in Listing 7 for the CREATE_ACTION.

CHECK_OBJECT

The CHECK_OBJECT procedure has up to nine possible input values and one output value. Again, due to placement of the arguments in order to use the default values you must use positional specifications for any calls to this procedure unless you specify values for all of the parameters. Table 6 shows the parameters for the CHECK_OBJECT procedure.

Table 6: CHECK_OBJECT Procedure IN and OUT Parameters

As you can see from doing a DESCRIBE on the procedure, the positioning of the CORRUPT_COUNT OUT variable after all of the values that have default values will force us to use positional nomenclature or specify values for all of the required input variables. Sometimes I wish the guys that write this stuff had to use it on a daily basis. AN example run of the procedure CHECK_OBJECT is shown in Listing 8. 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.

Listing 8 Example Execution of the CHECK_OBJECT Procedure

Can we get by without specifying the out value?

SQL> execute dbms_repair.check_object('GRAPHICS_DBA','INTERNAL_GRAPHICS');
BEGIN dbms_repair.check_object('GRAPHICS_DBA','INTERNAL_GRAPHICS'); END; 

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'CHECK_OBJECT'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Nope, I guess not, let's put a variable and see what happens.

SQL> var x number;
SQL> execute dbms_repair.check_object('GRAPHICS_DBA','INTERNAL_GRAPHICS',:x);
BEGIN dbms_repair.check_object('GRAPHICS_DBA','INTERNAL_GRAPHICS',:x); END; 

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'CHECK_OBJECT'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Nope, Oracle is too smart for us. Let's use positional notation once again due to the inconsideration of the developer:

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

PL/SQL procedure successfully completed. 

SQL> print x

        X
---------
        0

So all that work just to find out we don't have a problem…oh well, I guess it is better not to have a problem.

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.