Oracle中如何检测索引上的孤立键值orphan key

Posted by PDSERVICE on Jun 19, 2014 In index corrupted 0 comments

1- 首先创建一个孤立键表

BEGIN

DBMS_REPAIR.ADMIN_TABLES (

TABLE_NAME => 'ORPHAN_KEY_TABLE',

TABLE_TYPE => dbms_repair.orphan_table,

ACTION => dbms_repair.create_action,

TABLESPACE => 'USERS');

END;

2 - 查找对应索引上的孤立键值

SET SERVEROUTPUT ON

DECLARE num_orphans INT;

BEGIN

num_orphans := 0;

DBMS_REPAIR.DUMP_ORPHAN_KEYS (

SCHEMA_NAME => 'SCOTT',

OBJECT_NAME => 'PK_DEPT',

OBJECT_TYPE => dbms_repair.index_object,

REPAIR_TABLE_NAME => 'REPAIR_TABLE',

ORPHAN_TABLE_NAME=> 'ORPHAN_KEY_TABLE',

KEY_COUNT => num_orphans);

DBMS_OUTPUT.PUT_LINE('orphan key count: ' || TO_CHAR(num_orphans));

END;

3- 标记坏块

BEGIN

DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (

SCHEMA_NAME => 'SCOTT',

OBJECT_NAME => 'DEPT',

OBJECT_TYPE => dbms_repair.table_object,

FLAGS => dbms_repair.skip_flag);

END;

You are here