Table of Contents
This chapter briefly introduces the DBMS_REPAIR package, and describes how to use the functions of the package.
DBMS_REPAIR is used to check for and repair data block corruption in a table or an index.
The following are constants defined in the DBMS_REPAIR package:
ALL_INDEX_ID
Specifies a schema object ID to clean up all indexes.
ALL_INDEX_ID CONSTANT BINARY_INTEGER := 0
LOCK_NOWAIT
Requests DML lock on dependent tables, partitions, or subpartitions in NOWAIT mode.
LOCK_NOWAIT CONSTANT BOOLEAN := true
LOCK_WAIT
Requests DML lock on dependent tables, partitions, or subpartitions in WAIT mode.
LOCK_NOWAIT CONSTANT BOOLEAN := false
This section describes the functions provided by the DBMS_REPAIR package.
Cleans up failed or suspended index online-builds or online-rebuilds.
Details about the ONLINE_INDEX_CLEAN function are as follows:
Prototype
DBMS_REPAIR.ONLINE_INDEX_CLEAN
(
object_id IN BINARY_INTEGER DEFAULT ALL_INDEX_ID,
wait_for_lock IN BOOLEAN DEFAULT LOCK_WAIT
)
RETURN BOOLEAN;
Parameter
| Parameter | Description |
|---|---|
| object_id | Schema object ID of the index to clean up. If set to ALL_INDEX_ID, all qualified object IDs are cleaned. |
| wait_for_lock | The LOCK mode used to request DML locks on index-dependent tables, partitions, or subpartitions.
|
Return Value
| Value | Description |
|---|---|
| TRUE | Returned if all indexes have been cleaned up. |
| FALSE | Returned if one or more indexes have failed to be not cleaned up. |
Example
DECLARE
cleaned BOOLEAN;
BEGIN
cleaned := false;
WHILE cleaned = false
LOOP
cleaned := DBMS_REPAIR.ONLINE_INDEX_CLEAN(DBMS_REPAIR.ALL_INDEX_ID,
DBMS_REPAIR.LOCK_WAIT);
DBMS_LOCK.SLEEP(10);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
/
PSM completed
SQL>