18. Dbms_repair (for detecting, repairing corrupted data blocks on tables and indexes)

1. Overview

Function: Used to detect and repair corrupted data blocks on tables and indexes.

2, the composition of the package

1), Admin_tables
Syntax: Dbms_repair.admin_tables (table_name in Varchar2,table_type in Binary_integer,action in Binary_integer,tablespace In varchar2 default null);
Where table_name is used to specify the table name to be processed, you must specify the prefix orphan or repair,table_type specify the table type (orphan or repair_table).
The action specifies the administrative action to perform (establish table create_action, delete all row purge_action, delete table drop_action), tablespace to specify the table space where the table resides.
Example:
exec dbms_repair.admin_tables (' repair_table ', dbms_repair.repair_table,dbms_repair.create_action, ' system ');
exec dbms_repair.admin_tables (' orphan_table ', dbms_repair.orphan_table,dbms_repair.create_action, ' system ');
After executing the first statement, the above example establishes a repair table repair_table, which is used to hold information about the corrupted data block, and after the second statement is executed, a lone table orphan_table is used to hold the index entry information that points to the corrupted data block.

2), Check_object
Function: Used to examine a specific object and fill in the damage information into the repair table.
Syntax: Dbms_repair.check_object (schema_name in varchar2,object_name with varchar2,partition_name in varchar2 default NULL, Object_type in Binary_integer default Tables_object,
Repair_table_name in varchar2 default ' repair_table ', "Flags in Binary_integer" default Null,relative_fno in Binary_integer Default NULL,
Block_start in Binary_integer default null,block_end on binary_integer default null,corrupt_count out Binary_integer);
Where schema_name specifies the schema name of the object to check, object_name specifies the name of the object to check, Partition_name is used to specify the partition name to check, and object_type specifies the type of object to check (Table_ Object or Index_object),
REPAIR_TABLE_NAME Specifies the repair table to be filled in, flags reserved for future use, RELATIVE_FNO specifies the relative file number, Block_start specifies the starting block number to check, and Block_end specifies the end block number to check.
The corrupt_count is used to return the number of corrupted blocks.
Example:
var corr_count number;
EXEC dbms_repair.check_object (' Scott ', ' EMP ', corrupt_count=>:corr_count);
Print Corr_count

3), Dump_orphan_keys
Function: Used to report an index entry that points to a damaged block of data and inserts information from the corresponding index entry into the lone table.
Syntax: Dbms_repair.dump_orphan_keys (schema_name in Varchar2,object_name in Varchar2,
Partition_name in varchar2 default Null,object_type in Binary_integer default Index_object,
Repair_table_name in varchar2 default ' repair_table ', orphan_table_name in varchar2 default ' orphan_keys_table ',
Flags in Binary_integer default Null,key_count out Binary_integer);
Where object_type specifies the object type (index_object), repair_table_name specifies the repair table name, orphan_table_name specifies the name of the orphan table, and Key_count is used to return the number of index entries.
Example:
var key_count number
EXEC dbms_repair.dump_orphan_keys (' Scott ', ' pk_emp ',orphan_table_name=> ' orphan_table ', Key_count=>:key_ count);
Print Key_count

4), Fix_corrupt_blocks
Effect: Repairs corrupted blocks of data that are generated after Check_object has been executed.
Syntax: Dbms_repair.fix_corrupt_blocks (schema_name in Varchar2,object_name in Varchar2,partition_name in varchar2 default Null
Object_type in Binary_integer default table_object,repair_table_name in varchr2 default ' repair_table ',
Flags in Binary_integer default Null,fix_count out Binary_integer);
Where object_type specifies the object type (table_object), Fix_count returns the number of repaired data blocks.
Example:
var fix_count number
EXEC dbms_repair.fix_corrup_block (' Scott ', ' EMP ', fix_count=>:fix_count);
Print Fix_count

5), rebuild_freelists
Function: Used to reconstruct the idle list of the specified object
Syntax: dbms_repair.rebuild_freelists (schema_name in Varchar2,object_name in Varchar2,partition_name in varchar2 default Null
Object_type in Binary_integer default table_object);
Where object_type specifies the object type (table_object).
Example: Exec dbms_repair.rebuild_freelists (' Scott ', ' EMP ');

6), Skip_corrupt_blocks
Function: Used to specify that a damaged block is skipped when scanning an object (table or index).
Syntax: Dbms_repair.skip_corrupt_blocks (schema_name in Varchar2,object_name in Varchar2,object_type in Binary_integer Default Table_object,
Flag in Binary_integer default Skip_flag);
Where object_type specifies the object type (table_object), flags specifies whether to skip the corrupted block (Skip_flao skips, No_skip_flag does not skip)
Example: Exec dbms_repair.skip_corrupt_blocks (' Scott ', ' EMP ');

7), Segment_fix_status
function: Used to repair damage to the bitmap entry
Syntax: Dbms_repair.segment_fix_status (Segment_owner in Varchar2,segment_name in Varchar2,segment_type in Binary_integer Default Table_object,
File_number in Binary_integer default null,block_number in Binary_integer default NULL,
Status_value in Binary_integer default null,partition_name in varchar2 default null);
Where Segment_owner specifies the segment owner, segment_name specifies the segment name, Segment_type specifies the segment type, and file_number the relative file number where the data block resides,
Block_number Specifies a block number, Status_value specifies the block state value (1: Full block, 2:0~25%,3:25%~50%,4:50%~75%,5:75%~100%)
PARTITION_NAME specifies the partition name.
Example: exe dbms_repair.segment_fix_status (' sys ', ' mytab ');

18. Dbms_repair (for detecting, repairing corrupted data blocks on tables and indexes)