Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

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.

ArgumentName

Type

In/Out

DefaultValue?

SCHEMA_NAME

VARCHAR2

IN

 

OBJECT_NAME

VARCHAR2

IN

 

PARTITION_NAME

VARCHAR2

IN

NULL

OBJECT_TYPE

BINARY_INTEGER

IN

INDEX_OBJECT

REPAIR_TABLE_NAME

VARCHAR2

IN

'REPAIR_TABLE'

ORPHAN_TABLE_NAME

VARCHAR2

IN

'ORPHAN_KEY_TABLE'

FLAGS

BINARY_INTEGER

IN

NULL

KEY_COUNT

BINARY_INTEGER

OUT

 

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.

Argument

Type

In/Out

DefaultValue?

SCHEMA_NAME

VARCHAR2

IN

 

OBJECT_NAME

VARCHAR2

IN

 

PARTITION_NAME

VARCHAR2

IN

NULL

OBJECT_TYPE

BINARY_INTERGER

IN

TABLE_OBJECT

REPAIR_TABLE_NAME

VARCHAR2

IN

'REPAIR_TABLE'

FLAGS

BINARY_INTEGER

IN

NULL

FIX_COUNT

BINARY_INTERGER

OUT

 

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.

Argument

Type

In/Out

Default?

SCHEMA_NAME

VARCHAR2

IN

 

OBJECT_NAME

VARCHAR2

IN

 

PARTITION_NAME

VARCHAR2

IN

NULL

OBJECT_TYPE

BINARY_INTEGER

IN

TABLE_OBJECT

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.

Argument

Type

In/Out

DefaultValue?

SCHEMA_NAME

VARCHAR2

IN

 

OBJECT_NAME

VARCHAR2

IN

 

OBJECT_TYPE

BINARY_INTEGER

IN

TABLE_OBJECT

FLAGS

BINARY_INTEGER

IN

SKIP_FLAG

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.

 


 

 
��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.