33
DBMS_REPAIR

DBMS_REPAIR 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.

Note:
The DBMS_REPAIR package is intended for use by database administrators only. It is not intended for use by application developers.

See Also:
For detailed information about using the DBMS_REPAIR package, see Oracle8i Administrator's Guide.

Security

The package is owned by SYS. Execution privilege is not granted to other users.

Enumeration Types

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.

Table 33-1 lists the parameters and the enumerated constants.

Table 33-1 DBMS_REPAIR Enumeration Types

Parameter	Constant
object_type	TABLE_OBJECT, INDEX_OBJECT, CLUSTER_OBJECT
action	CREATE_ACTION, DROP_ACTION, PURGE_ACTION
table_type	REPAIR_TABLE, ORPHAN_TABLE
flags	SKIP_FLAG, NOSKIP_FLAG

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.

Exceptions

Table 33-2 DBMS_REPAIR Exceptions

Exception	Description	Action
942	Reported by `DBMS_REPAIR.ADMIN_TABLES` during a `DROP_ACTION` when the specified table doesn't exist.
955	Reported by `DBMS_REPAIR`. `CREATE_ACTION` when the specified table already exists.
24120	An invalid parameter was passed to the specified `DBMS_REPAIR` procedure.	Specify a valid parameter value or use the parameter's default.
24122	An incorrect block range was specified.	Specify correct values for the `BLOCK_START` and `BLOCK_END` parameters.
24123	An attempt was made to use the specified feature, but the feature is not yet implemented.	Do not attempt to use the feature.
24124	An invalid `ACTION` parameter was specified.	Specify `CREATE_ACTION`, `PURGE_ACTION` or `DROP_ACTION` for the `ACTION` parameter.
24125	An attempt was made to fix corrupt blocks on an object that has been dropped or truncated since `DBMS_REPAIR`.`CHECK_OBJECT` was run.	Use `DBMS_REPAIR`.`ADMIN_TABLES` to purge the repair table and run `DBMS_REPAIR`.`CHECK_OBJECT` to determine whether there are any corrupt blocks to be fixed.
24127	`TABLESPACE` parameter specified with an `ACTION` other than `CREATE_ACTION`.	Do not specify `TABLESPACE` when performing actions other than `CREATE_ACTION`.
24128	A partition name was specified for an object that is not partitioned.	Specify a partition name only if the object is partitioned.
24129	An attempt was made to pass a table name parameter without the specified prefix.	Pass a valid table name parameter.
24130	An attempt was made to specify a repair or orphan table that does not exist.	Specify a valid table name parameter.
24131	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.
24132	An attempt was made to specify a table name is greater than 30 characters long.	Specify a valid table name parameter.

Summary of Subprograms

Table 33-3 DBMS_REPAIR Package Subprograms

Subprogram	Description
ADMIN_TABLES procedure	Provides administrative functions for the `DBMS_REPAIR` package repair and orphan key tables, including create, purge, and drop functions.
CHECK_OBJECT procedure	Detects and reports corruptions in a table or index.
DUMP_ORPHAN_KEYS procedure	Reports on index entries that point to rows in corrupt data blocks.
FIX_CORRUPT_BLOCKS procedure	Marks blocks software corrupt that have been previously detected as corrupt by `CHECK_OBJECT`.
REBUILD_FREELISTS procedure	Rebuilds an object's freelists.
SKIP_CORRUPT_BLOCKS procedure	Sets whether to ignore blocks marked corrupt during table and index scans or to report `ORA-1578` when blocks marked corrupt are encountered.

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 33-4 ADMIN_TABLES Procedure Parameters

Parameter	Description
table_name	Name of the table to be processed. Defaults to `ORPHAN_KEY_TABLE` or `REPAIR_TABLE` based on the specified `table_type`. When specified, the table name must have the appropriate prefix: `ORPHAN_` or `REPAIR_`.
table_type	Type of table; must be either `ORPHAN_TABLE` or `REPAIR_TABLE`. See "Enumeration Types".
action	Indicates what administrative action to perform. Must be either `CREATE_ACTION`, `PURGE_ACTION`, or `DROP_ACTION`. If the table already exists, and if `CREATE_ACTION` is specified, then an error is returned. `PURGE_ACTION` indicates to delete all rows in the table that are associated with non-existent objects. If the table does not exist, and if `DROP_ACTION` is specified, then an error is returned. When `CREATE_ACTION` and `DROP_ACTION` are specified, an associated view named `DBA_<table_name>` is created and dropped respectively. The view is defined so that rows associated with non-existent objects are eliminated. Created in the `SYS` schema. See "Enumeration Types".
tablespace	Indicates the tablespace to use when creating a table. By default, the `SYS` default tablespace is used. An error is returned if the tablespace is specified and if the action is not `CREATE_ACTION`.

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. You may optionally specify a DBA range, partition name, or subpartition name when you want to check a portion of an 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 33-5 CHECK_OBJECT Procedure Parameters

Parameter	Description
schema_name	Schema name of the object to be checked.
object_name	Name of the table or index to be checked.
partition_name	Partition or subpartition name to be checked. If this is a partitioned object, and if `partition_name` is not specified, then all partitions and subpartitions are checked. If this is a partitioned object, and if the specified partition contains subpartitions, then all subpartitions are checked.
object_type	Type of the object to be processed. This must be either `TABLE_OBJECT` (default) or `INDEX_OBJECT`. See "Enumeration Types".
repair_table_name	Name of the repair table to be populated. The table must exist in the `SYS` schema. Use the `admin_tables` procedure to create a repair table. The default name is `REPAIR_TABLE`.
flags	Reserved for future use.
relative_fno	Relative file number: Used when specifying a block range.
block_start	First block to process if specifying a block range. May be specified only if the object is a single table, partition, or subpartition.
block_end	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 `block_start` or `block_end` is specified, then the other defaults to the first or last block in the file respectively.
corrupt_count	Number of corruptions reported.

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 33-6 DUMP_ORPHAN_KEYS Procedure Parameters

Parameter	Description
schema_name	Schema name.
object_name	Object name.
partition_name	Partition or subpartition name to be processed. If this is a partitioned object, and if `partition_name` is not specified, then all partitions and subpartitions are processed. If this is a partitioned object, and if the specified partition contains subpartitions, then all subpartitions are processed.
object_type	Type of the object to be processed. The default is `INDEX_OBJECT` See "Enumeration Types".
repair_table_name	Name of the repair table that has information regarding corrupt blocks in the base table. The specified table must exist in the `SYS` schema. The `admin_tables` procedure is used to create the table.
orphan_table_name	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 `SYS` schema. The `admin_tables` procedure is used to create the table.
flags	Reserved for future use.
key_count	Number of index entries processed.

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 33-7 FIX_CORRUPT_BLOCKS Procedure Parameters

Parameter	Description
schema_name	Schema name.
object_name	Name of the object with corrupt blocks to be fixed.
partition_name	Partition or subpartition name to be processed. If this is a partitioned object, and if `partition_name` is not specified, then all partitions and subpartitions are processed. If this is a partitioned object, and if the specified partition contains subpartitions, then all subpartitions are processed.
object_type	Type of the object to be processed. This must be either `TABLE_OBJECT` (default) or `INDEX_OBJECT`. See "Enumeration Types".
repair_table_name	Name of the repair table with the repair directives. Must exist in the `SYS` schema.
flags	Reserved for future use.
fix_count	Number of blocks fixed.

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,   
   partition_name IN VARCHAR2       DEFAULT NULL,
   object_type    IN BINARY_INTEGER DEFAULT TABLE_OBJECT);

Parameters

Table 33-8 REBUILD_FREELISTS Procedure Parameters

Parameter	Description
schema_name	Schema name.
object_name	Name of the object whose freelists are to be rebuilt.
partition_name	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.
object_type	Type of the object to be processed. This must be either `TABLE_OBJECT` (default) or `INDEX_OBJECT`. See "Enumeration Types".

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.

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 33-9 SKIP_CORRUPT_BLOCKS Procedure Parameters

Parameter	Description
schema_name	Schema name of the object to be processed.
object_name	Name of the object.
partition_name (optional)	Partition or subpartition name to be processed. If this is a partitioned object, and if `partition_name` is not specified, then all partitions and subpartitions are processed. If this is a partitioned object, and if the specified partition contains subpartitions, then all subpartitions are processed.
object_type	Type of the object to be processed. This must be either `TABLE_OBJECT` (default) or `CLUSTER_OBJECT`. See "Enumeration Types".
flags	If `SKIP_FLAG` is specified, then it turns on the skip of software corrupt blocks for the object during index and table scans. If `NOSKIP_FLAG` is specified, then scans that encounter software corrupt blocks return an `ORA`-`1578`. See "Enumeration Types".

33DBMS_REPAIR

Security

Enumeration Types

Table 33-1 DBMS_REPAIR Enumeration Types

Exceptions

Table 33-2 DBMS_REPAIR Exceptions

Summary of Subprograms

Table 33-3 DBMS_REPAIR Package Subprograms

ADMIN_TABLES procedure

Syntax

Parameters

Table 33-4 ADMIN_TABLES Procedure Parameters

CHECK_OBJECT procedure

Syntax

Parameters

Table 33-5 CHECK_OBJECT Procedure Parameters

DUMP_ORPHAN_KEYS procedure

Syntax

Parameters

Table 33-6 DUMP_ORPHAN_KEYS Procedure Parameters

FIX_CORRUPT_BLOCKS procedure

Syntax

Parameters

Table 33-7 FIX_CORRUPT_BLOCKS Procedure Parameters

REBUILD_FREELISTS procedure

Syntax

Parameters

Table 33-8 REBUILD_FREELISTS Procedure Parameters

SKIP_CORRUPT_BLOCKS procedure

Syntax

Parameters

Table 33-9 SKIP_CORRUPT_BLOCKS Procedure Parameters

33
DBMS_REPAIR