155 DBMS_REPAIR
The DBMS_REPAIR
package contains data corruption repair procedures that enable you to detect and repair corrupt blocks in tables and indexes. You can address corruptions where possible and continue to use objects while you attempt to rebuild or repair them.
This chapter contains the following topics:
See Also:
For detailed information about using the DBMS_REPAIR
package, see Oracle Database
Administrator’s Guide
155.1 DBMS_REPAIR Overview
The DBMS_REPAIR
package is intended for use by database administrators only. It is not intended for use by application developers.
155.2 DBMS_REPAIR Security Model
The package is owned by SYS
. Execution privilege is not granted to other users.
155.3 DBMS_REPAIR Constants
The DBMS_REPAIR
package defines several enumerated constants that should be used for specifying parameter values. Enumerated constants must be prefixed with the package name. For example, DBMS_REPAIR
.TABLE_OBJECT
.
The following table lists the parameters and the enumerated constants.
Table 155-1 DBMS_REPAIR Parameters with Enumerated Constants
Parameter | Option | Type | Description |
---|---|---|---|
|
|
|
- |
|
|
|
- |
|
|
|
- |
|
|
|
- |
|
|
|
Clean up all objects that qualify |
|
|
|
Specifies whether to try getting DML locks on underlying table [[sub]partition] object |
Note:
The default table_name
will be REPAIR_TABLE
when table_type
is REPAIR_TABLE
, and will be ORPHAN_KEY_TABLE
when table_type
is ORPHAN_TABLE
.
155.4 DBMS_REPAIR Operating Notes
The procedure to create the ORPHAN_KEYS_TABLE
is similar to the one used to create the REPAIR_TABLE
.
CONNECT / AS SYSDBA; EXEC DBMS_REPAIR.ADMIN_TABLES('ORPHAN_KEYS_TABLE', DBMS_REPAIR.ORPHAN_TABLE, DBMS_REPAIR.CREATE_ACTION); EXEC DBMS_REPAIR.ADMIN_TABLES('REPAIR_TABLE', DBMS_REPAIR.REPAIR_TABLE, DBMS_REPAIR.CREATE_ACTION); DESCRIBE ORPHAN_KEYS_TABLE; DESCRIBE REPAIR_TABLE; SELECT * FROM ORPHAN_KEYS_TABLE; SELECT * FROM REPAIR_TABLE;
The DBA would create the repair and orphan keys tables once. Subsequent executions of the CHECK_OBJECT Procedure would add rows into the appropriate table indicating the types of errors found.
The name of the repair and orphan keys tables can be chosen by the user, with the following restriction: the name of the repair table must begin with the 'REPAIR_
' prefix, and the name of the orphan keys table must begin with the 'ORPHAN_
' prefix. The following code is also legal:
CONNECT / AS SYSDBA; EXEC DBMS_REPAIR.ADMIN_TABLES('ORPHAN_FOOBAR', DBMS_REPAIR.ORPHAN_TABLE, DBMS_REPAIR.CREATE_ACTION); EXEC DBMS_REPAIR.ADMIN_TABLES('REPAIR_ABCD', DBMS_REPAIR.REPAIR_TABLE, DBMS_REPAIR.CREATE_ACTION); DESCRIBE ORPHAN_FOOBAR; DESCRIBE REPAIR_ABCD; SELECT * FROM ORPHAN_FOOBAR; SELECT * FROM REPAIR_ABCD;
When invoking the CHECK_OBJECT Procedure the name of the repair and orphan keys tables that were created should be specified correctly, especially if the default values were not used in the ADMIN_TABLES Procedure or CREATE_ACTION
.
Other actions in the ADMIN_TABLES Procedure can be used to purge/delete the REPAIR_TABLE
and the ORPHAN_KEYS_TABLE
.
155.5 DBMS_REPAIR Exceptions
The table in this topic describes the exceptions raised by the DDBMS_REPAIR subprograms.
Table 155-2 DBMS_REPAIR Exceptions
Exception | Description | Action |
---|---|---|
|
Reported by |
- |
|
Reported by |
- |
|
An invalid parameter was passed to the specified |
Specify a valid parameter value or use the parameter's default. |
|
An incorrect block range was specified. |
Specify correct values for the |
|
An attempt was made to use the specified feature, but the feature is not yet implemented. |
Do not attempt to use the feature. |
|
An invalid |
Specify |
|
An attempt was made to fix corrupt blocks on an object that has been dropped or truncated since |
Use |
|
|
Do not specify |
|
A partition name was specified for an object that is not partitioned. |
Specify a partition name only if the object is partitioned. |
|
An attempt was made to pass a table name parameter without the specified prefix. |
Pass a valid table name parameter. |
|
An attempt was made to specify a repair or orphan table that does not exist. |
Specify a valid table name parameter. |
|
An attempt was made to specify a repair or orphan table that does not have a correct definition. |
Specify a table name that refers to a properly created table. |
|
An attempt was made to specify a table name is greater than 30 characters long. |
Specify a valid table name parameter. |
155.6 DBMS_REPAIR Examples
This topic shows examples of DBMS_REPAIR usage.
/* Fix the bitmap status for all the blocks in table mytab in schema sys */ EXECUTE DBMS_REPAIR.SEGMENT_FIX_STATUS('SYS', 'MYTAB'); /* Mark block number 45, filenumber 1 for table mytab in sys schema as FULL.*/ EXECUTE DBMS_REPAIR.SEGMENT_FIX_STATUS('SYS', 'MYTAB', TABLE_OBJECT,1, 45, 1);
155.7 Summary of DBMS_REPAIR Subprograms
This table lists the DBMS_REPAIR
subprograms and briefly describes them.
Table 155-3 DBMS_REPAIR Package Subprograms
Subprogram | Description |
---|---|
Provides administrative functions for the |
|
Detects and reports corruptions in a table or index |
|
Reports on index entries that point to rows in corrupt data blocks |
|
Marks blocks software corrupt that have been previously detected as corrupt by |
|
Performs a manual cleanup of failed or interrupted online index builds or rebuilds |
|
Rebuilds an object's freelists |
|
Fixes the corrupted state of a bitmap entry |
|
Sets whether to ignore blocks marked corrupt during table and index scans or to report |
155.7.1 ADMIN_TABLES Procedure
This procedure provides administrative functions for the DBMS_REPAIR
package repair and orphan key tables.
Syntax
DBMS_REPAIR.ADMIN_TABLES ( table_name IN VARCHAR2, table_type IN BINARY_INTEGER, action IN BINARY_INTEGER, tablespace IN VARCHAR2 DEFAULT NULL);
Parameters
Table 155-4 ADMIN_TABLES Procedure Parameters
Parameter | Description |
---|---|
|
Name of the table to be processed. Defaults to |
|
Type of table; must be either See "Constants". |
|
Indicates what administrative action to perform. Must be either When Created in the See "Constants". |
|
Indicates the tablespace to use when creating a table. By default, the |
155.7.2 CHECK_OBJECT Procedure
This procedure checks the specified objects and populates the repair table with information about corruptions and repair directives.
Validation consists of block checking all blocks in the object.
Syntax
DBMS_REPAIR.CHECK_OBJECT ( 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 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 IN BINARY_INTEGER DEFAULT NULL, corrupt_count OUT BINARY_INTEGER);
Parameters
Table 155-5 CHECK_OBJECT Procedure Parameters
Parameter | Description |
---|---|
|
Schema name of the object to be checked. |
|
Name of the table or index to be checked. |
|
Partition or subpartition name to be checked. If this is a partitioned object, and if |
|
Type of the object to be processed. This must be either See "Constants". |
|
Name of the repair table to be populated. The table must exist in the |
|
Reserved for future use. |
|
Relative file number: Used when specifying a block range. |
|
First block to process if specifying a block range. May be specified only if the object is a single table, partition, or subpartition. |
|
Last block to process if specifying a block range. May be specified only if the object is a single table, partition, or subpartition. If only one of |
|
Number of corruptions reported. |
Usage Notes
You may optionally specify a DBA range, partition name, or subpartition name when you want to check a portion of an object.
155.7.3 DUMP_ORPHAN_KEYS Procedure
This procedure reports on index entries that point to rows in corrupt data blocks. For each such index entry encountered, a row is inserted into the specified orphan table.
If the repair table is specified, then any corrupt blocks associated with the base table are handled in addition to all data blocks that are marked software corrupt. Otherwise, only blocks that are marked corrupt are handled.
This information may be useful for rebuilding lost rows in the table and for diagnostic purposes.
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);
Parameters
Table 155-6 DUMP_ORPHAN_KEYS Procedure Parameters
Parameter | Description |
---|---|
|
Schema name. |
|
Object name. |
|
Partition or subpartition name to be processed. If this is a partitioned object, and if |
|
Type of the object to be processed. The default is See "Constants". |
|
Name of the repair table that has information regarding corrupt blocks in the base table. The specified table must exist in the |
|
Name of the orphan key table to populate with information regarding each index entry that refers to a row in a corrupt data block. The specified table must exist in the |
|
Reserved for future use. |
|
Number of index entries processed. |
155.7.4 FIX_CORRUPT_BLOCKS Procedure
This procedure fixes the corrupt blocks in specified objects based on information in the repair table that was previously generated by the CHECK_OBJECT Procedure.
Prior to effecting any change to a block, the block is checked to ensure the block is still corrupt. Corrupt blocks are repaired by marking the block software corrupt. When a repair is effected, the associated row in the repair table is updated with a fix timestamp.
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 VARCHAR2 DEFAULT 'REPAIR_TABLE', flags IN BINARY_INTEGER DEFAULT NULL, fix_count OUT BINARY_INTEGER);
Parameters
Table 155-7 FIX_CORRUPT_BLOCKS Procedure Parameters
Parameter | Description |
---|---|
|
Schema name. |
|
Name of the object with corrupt blocks to be fixed. |
|
Partition or subpartition name to be processed. If this is a partitioned object, and if |
|
Type of the object to be processed. This must be either See "Constants". |
|
Name of the repair table with the repair directives. Must exist in the |
|
Reserved for future use. |
|
Number of blocks fixed. |
Related Topics
155.7.5 ONLINE_INDEX_CLEAN Function
This function performs a manual cleanup of failed or interrupted online index builds or rebuilds.
This action is also performed periodically by SMON, regardless of user-initiated cleanup.
This function returns TRUE
if all indexes specified were cleaned up and FALSE
if one or more indexes could not be cleaned up.
Syntax
DBMS_REPAIR.ONLINE_INDEX_CLEAN ( object_id IN BINARY_INTEGER DEFAULT ALL_INDEX_ID, wait_for_lock IN BINARY_INTEGER DEFAULT LOCK_WAIT) RETURN BOOLEAN;
Parameters
Table 155-8 ONLINE_INDEX_CLEAN Function Parameters
Parameter | Description |
---|---|
|
Object id of index to be cleaned up. The default cleans up all object ids that qualify. |
|
This parameter specifies whether to try getting DML locks on underlying table [[sub]partition] object. The default retries up to an internal retry limit, after which the lock get will give up. If |
155.7.6 REBUILD_FREELISTS Procedure
This procedure rebuilds the freelists for the specified object.
All free blocks are placed on the master freelist. All other freelists are zeroed.
If the object has multiple freelist groups, then the free blocks are distributed among all freelists, allocating to the different groups in round-robin fashion.
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);
Parameters
Table 155-9 REBUILD_FREELISTS Procedure Parameters
Parameter | Description |
---|---|
|
Schema name. |
|
Name of the object whose freelists are to be rebuilt. |
|
Partition or subpartition name whose freelists are to be rebuilt. If this is a partitioned object, and partition_name is not specified, then all partitions and subpartitions are processed. If this is a partitioned object, and the specified partition contains subpartitions, then all subpartitions are processed. |
|
Type of the object to be processed. This must be either See"Constants". |
155.7.7 SEGMENT_FIX_STATUS Procedure
With this procedure you can fix the corrupted state of a bitmap entry. The procedure either recalculates the state based on the current contents of the corresponding block or sets the state to a specific value.
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,);
Parameters
Table 155-10 SEGMENT_FIX_STATUS Procedure Parameters
Parameter | Description |
---|---|
|
Schema name of the segment. |
|
Segment name. |
|
Optional. Name of an individual partition. |
|
Optional Type of the segment (for example, |
|
(optional) The tablespace-relative file number of the data block whose status has to be fixed. If omitted, all the blocks in the segment will be checked for state correctness and fixed. |
|
(optional) The file-relative block number of the data block whose status has to be fixed. If omitted, all the blocks in the segment will be checked for state correctness and fixed. |
|
(optional) The value to which the block status described by the
The status for bitmap blocks, segment headers, and extent map blocks cannot be altered. The status for blocks in a fixed hash area cannot be altered. For index blocks, there are only two possible states: 1 = block is full and 3 = block has free space. |
155.7.8 SKIP_CORRUPT_BLOCKS Procedure
This procedure enables or disables the skipping of corrupt blocks during index and table scans of the specified object.
When the object is a table, skip applies to the table and its indexes. When the object is a cluster, it applies to all of the tables in the cluster, and their respective indexes.
Note:
When Oracle performs an index range scan on a corrupt index after DBMS_REPAIR.SKIP_CORRUPT_BLOCKS has been set for the base table, corrupt branch blocks and root blocks are not skipped. Only corrupt non-root leaf blocks are skipped.
Syntax
DBMS_REPAIR.SKIP_CORRUPT_BLOCKS ( schema_name IN VARCHAR2, object_name IN VARCHAR2, object_type IN BINARY_INTEGER DEFAULT TABLE_OBJECT, flags IN BINARY_INTEGER DEFAULT SKIP_FLAG);
Parameters
Table 155-11 SKIP_CORRUPT_BLOCKS Procedure Parameters
Parameter | Description |
---|---|
|
Schema name of the object to be processed. |
|
Name of the object. |
|
Type of the object to be processed. This must be either See "Constants". |
|
If See"Constants". |