| General Information |
| Source |
{ORACLE_HOME}/rdbms/admin/dbmsrpr.sql |
| First Available |
8.1.5 |
| Constants |
| Actions |
Data Type |
Value |
| CREATE_ACTION |
BINARY_INTEGER |
1 |
| DROP_ACTION |
BINARY_INTEGER |
3 |
| PURGE_ACTION |
BINARY_INTEGER |
2 |
| |
| Flags |
Data Type |
Value |
| NOSKIP_FLAG |
BINARY_INTEGER |
2 |
| SKIP_FLAG |
BINARY_INTEGER |
1 |
| |
| Lock Wait |
Data Type |
Value |
| LOCK_NOWAIT |
BINARY_INTEGER |
0 |
| LOCK_WAIT |
BINARY_INTEGER |
1 |
| |
| Object ID |
Data Type |
Value |
| ALL_INDEX_ID |
BINARY_INTEGER |
0 |
| |
| Object Types |
Data Type |
Value |
| CLUSTER_OBJECT |
BINARY_INTEGER |
4 |
| INDEX_OBJECT |
BINARY_INTEGER |
2 |
| TABLE_OBJECT |
BINARY_INTEGER |
1 |
| |
| Table Types |
Data Type |
Value |
| ORPHAN_TABLE |
BINARY_INTEGER |
2 |
| REPAIR_TABLE |
BINARY_INTEGER |
1 |
|
| Dependencies |
| DBMS_ASSERT |
DBMS_REPAIR_LIB |
DBMS_SQL |
DBMS_SYS_ERROR |
|
| Exceptions |
| Error Code |
Reason |
| 00942 |
Reported by DBMS_REPAIR.ADMIN_TABLES during a DROP_ACTION when the specified table doesn't exist |
| 00955 |
Reported by DBMS_REPAIR.CREATE_ACTION but specified table already exists |
| 24120 |
Invalid parameter was passed |
| 24122 |
Incorrect block range was specified |
| 24123 |
Ffeature is not yet implemented |
| 24124 |
Invalid ACTION parameter was specified |
| 24125 |
Object dropped or truncated since DBMS_REPAIR.CHECK_OBJECT was run |
| 24127 |
Tablespace parameter specified with an ACTION other than CREATE_ACTION |
| 24128 |
Object is not partitioned |
| 24129 |
Table name parameter without the specified prefix |
| 24130 |
Attempt was made to specify a repair or orphan table that does not exist |
| 24131 |
Attempt to specify and repair or orphan table that does not have a correct definition |
| 24132 |
Table names do not exceed 30 characters |
|
| Security Model |
Owned by SYS with no granted privileges |
| Subprograms |
|
| |
| ADMIN_TABLES |
| Create Orphan Key Table |
dbms_repair.admin_tables(
table_name IN VARCHAR2 DEFAULT 'GENERATE_DEFAULT_TABLE_NAME',
table_type IN BINARY_INTEGER,
action IN BINARY_INTEGER,
tablespace IN VARCHAR2 DEFAULT NULL); |
conn / as sysdba
exec dbms_repair.admin_tables('ORPHAN_KEYS_TABLE', dbms_repair.orphan_table, dbms_repair.create_action);
desc orphan_keys_table
-- after demos
exec dbms_repair.admin_tables('ORPHAN_KEYS_TABLE', dbms_repair.orphan_table, dbms_repair.drop_action); |
conn / as sysdba
exec dbms_repair.admin_tables('REPAIR_TABLE', dbms_repair.repair_table, dbms_repair.create_action, 'UWDATA');
desc repair_table
SQL> desc repair_table
Name Null? Type
---------------------- -------- ------------
OBJECT_ID NOT NULL NUMBER
TABLESPACE_ID NOT NULL NUMBER
RELATIVE_FILE_ID NOT NULL NUMBER
BLOCK_ID NOT NULL NUMBER
CORRUPT_TYPE NOT NULL NUMBER
SCHEMA_NAME NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)
BASEOBJECT_NAME VARCHAR2(30)
PARTITION_NAME VARCHAR2(30)
CORRUPT_DESCRIPTION VARCHAR2(2000)
REPAIR_DESCRIPTION VARCHAR2(200)
MARKED_CORRUPT NOT NULL VARCHAR2(10)
CHECK_TIMESTAMP NOT NULL DATE
FIX_TIMESTAMP DATE
REFORMAT_TIMESTAMP DATE |
-- after demos
exec dbms_repair.admin_tables('REPAIR_TABLE',1,3); |
| |
| CHECK_OBJECT |
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.
Before this demo is run the ADMIN_TABLES procedure must be used to create the repair table. |
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); |
conn uwclass/uwclass
CREATE TABLE badtab AS
SELECT DISTINCT object_name
FROM all_objects;
conn / as sysdba
set serveroutput on
DECLARE
i BINARY_INTEGER;
BEGIN
dbms_repair.check_object(schema_name=>'UWCLASS', object_name=>'SERVERS', corrupt_count=>i);
dbms_output.put_line(i);
END;
/ |
| |
| DUMP_ORPHAN_KEYS |
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. |
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); |
set serveroutput on
DECLARE
x BINARY_INTEGER;
BEGIN
dbms_repair.dump_orphan_keys('UWCLASS', 'SERVERS_PK', NULL, 2, 'REPAIR_TABLE', 'ORPHAN_KEYS_TABLE', 1, x);
dbms_output.put_line(x);
END;
/ |
| |
| FIX_CORRUPT_BLOCKS |
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. |
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); |
set serveroutput on
DECLARE
x BINARY_INTEGER;
BEGIN
dbms_repair.fix_corrupt_blocks('UWCLASS', 'SERVERS', NULL, 1, 'REPAIR_TABLE', 1, x);
dbms_output.put_line(x);
END;
/ |
| |
| ONLINE_INDEX_CLEAN |
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. |
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; |
DECLARE
isClean BOOLEAN;
BEGIN
isClean := FALSE;
WHILE isClean=FALSE LOOP
isClean := dbms_repair.online_index_clean(dbms_repair.all_index_id, dbms_repair.lock_wait);
dbms_lock.sleep(10);
END LOOP;
END;
/ |
| |
| REBUILD_FREELISTS |
Rebuilds 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.
This is discussed in Jonathan Lewis' Cost-Based Oracle Fundamentals
ISBN: 1-59059-636-6, pg 101 |
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); |
exec dbms_repair.rebuild_freelists('UWCLASS', 'SERVERS', NULL, dbms_repair.table_object);
-- tables in ASSM tablespaces do not have freelists
ERROR at line 1:
ORA-10614: Operation not allowed on this segment
ORA-06512: at "SYS.DBMS_REPAIR", line 400
ORA-06512: at line 1 |
| |
| REBUILD_SHC_INDEX |
| Rebuilds a sorted hash cluster index. This procedure will not recreate one that has been dropped as also demonstrated. |
dbms_repair.rebuild_shc_index(
segment_owner IN VARCHAR2,
cluster_name IN VARCHAR2); |
conn uwclass/uwclass
CREATE CLUSTER sorted_hc (
program_id NUMBER(3),
line_id NUMBER(10) SORT,
delivery_dt DATE SORT)
TABLESPACE uwdata
HASHKEYS 9
SIZE 750
HASH IS program_id;
CREATE TABLE shc_airplane (
program_id NUMBER(3),
line_id NUMBER(10) SORT,
delivery_dt DATE SORT,
customer_id VARCHAR2(3),
order_dt DATE)
CLUSTER sorted_hc (program_id, line_id, delivery_dt);
conn / as sysdba
exec dbms_repair.rebuild_shc_index('UWCLASS', 'SORTED_HC');
-- this demonstrates that it can not recreate a dropped SHC index
conn uwclass/uwclass
SELECT object_name, object_type, created
FROM user_objects
WHERE created > SYSDATE-10/1440
ORDER BY 2,1;
DROP INDEX SYS_IQ0000079026$$; -- Note: it is an Oracle bug that you can do this
conn / as sysdba
exec dbms_repair.rebuild_shc_index('UWCLASS', 'SORTED_HC');
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [25027], [0], [0], [], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_REPAIR", line 461
ORA-06512: at line 1 |
| |
| SEGMENT_FIX_STATUS |
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.
For segments with automatic ASSM, Oracle ignores attempts to change the PCTUSED setting.
If you alter the PCTFREE setting, then you must subsequently run the DBMS_REPAIR.SEGMENT_FIX_STATUS
procedure to implement the new setting on blocks already allocated to the segment. |
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);
| Status Value |
Description |
| 1 |
block is full |
| 2 |
block is 0-25% free |
| 3 |
block is 25-50% free |
| 4 |
block is 50-75% free |
| 5 |
block is 75-100% free |
|
| exec dbms_repair.segment_fix_status('UWCLASS', 'SERVERS', dbms_repair.table_object); |
| |
| SKIP_CORRUPT_BLOCKS |
| Enables or disables skipping 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 and indexes in the cluster. |
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); |
| exec dbms_repair.skip_corrupt_blocks('UWCLASS','SERVERS', dbms_repair.table_object, dbms_repair.noskip_flag); |
