DBMS_REPAIR EXAMPLE SCRIPT WITH PARTITION

DBMS_REPAIR EXAMPLE SCRIPT WITH PARTITION DBMS_REPAIR EXAMPLE SCRIPT WITH PARTITION (Doc ID 1436664.1) DBMS_REPAIR SCRIPT (Doc ID 556733.1) Run sqlplus with SYS user  1. Run sqlplus. Example: sqlplus '/ as sysdba' Instructions run the script from sqlplus Script 2.  Use the DBMS_REPAIR (CHECK_OBJECT/FIX_CORRUPT_BLOCKS/SKIP_CORRUPT_BLOCKS ) to find, mark and skip corrupted blocks. REM Used to create the REPAIR_TABLE from 10g and Above SYSAUX can be used as tablespace BEGIN DBMS_REPAIR.ADMIN_TABLES ( TABLE_NAME => 'REPAIR_TABLE', TABLE_TYPE => dbms_repair.repair_table, ACTION => dbms_repair.create_action, TABLESPACE => '&TABLESPACE_NAME'); END; REM Used to identified the corrupted blocks for Owner.Table (partition_name):  set serveroutput on DECLARE num_corrupt INT; BEGIN num_corrupt := 0; DBMS_REPAIR.CHECK_OBJECT ( SCHEMA_NAME => '&OWNER', OBJECT_NAME => '&TABLE', partition_name => '&PARTITION_NAME', REPAIR_TABLE_NAME => 'REPAIR_TABLE', corrupt_count => num_corrupt); DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt)); END;  REM Used to list corrupted blocks (Optional).  Select * from REPAIR_TABLE; REM Used to mark the identified blocks as corrupted blocks: DECLARE num_fix INT; BEGIN num_fix := 0; DBMS_REPAIR.FIX_CORRUPT_BLOCKS ( SCHEMA_NAME => '&OWNER', OBJECT_NAME=> '&TABLE', partition_name => '&PARTITION_NAME', OBJECT_TYPE => dbms_repair.table_object, REPAIR_TABLE_NAME => 'REPAIR_TABLE', FIX_COUNT=> num_fix); DBMS_OUTPUT.PUT_LINE('num fix: ' || to_char(num_fix)); END; REM Used to skip the corrupted blocks:  BEGIN DBMS_REPAIR.SKIP_CORRUPT_BLOCKS ( SCHEMA_NAME => '&OWNER', OBJECT_NAME => '&TABLE', OBJECT_TYPE => dbms_repair.table_object, FLAGS => dbms_repair.skip_flag); END; / 3.  Saving data on the table by skipping the corrupted blocks. Export/Truncate/Import table/partition==> &OWNER.&TABLE_NAME partition==> &PARTITION_NAME To export Table/Partition, run (From OS Prompt) exp userid=<owner>/<password> file=<export_file.dmp> tables=<TABLE_NAME>:<PARTITION_NAME> rows=y log=<export_file.log> To truncate the Table/Partition, run from sqlplus: ALTER TABLE &OWNER.&TABLE_NAME TRUNCATE PARTITION &PARTITION_NAME update global indexes; To import Table/Partition, run (From OS Prompt) imp userid=<owner>/<password> file=<export_file.dmp> tables=<TABLE_NAME>:<PARTITION_NAME> rows=y ignore=y log=<import_file.log> 4.  Rebuild any index which might become unusable after the truncate is executed and no maintained by        update global indexes option. SQL> select owner, index_name, index_type, status, table_name , table_owner from dba_indexes Where table_owner = '&OWNER' and Table_name = '&TABLE_NAME' and status = 'UNUSABLE'; To rebuild the index, run: SQL> alter index &OWNER.&INDEX_NAME rebuild; 5.  Remove the skip flag, run from sqlplus execute DBMS_REPAIR.SKIP_CORRUPT_BLOCKS(SCHEMA_NAME=>'&OWNER',OBJECT_NAME=>'&TABLE_NAME',flags=>sys.dbms_repair.NOSKIP_FLAG);