Bloque corrupto en un segmento

En la siguiente oeracion, se obtiene un error ORA 1578.

Create Table cpp As
Select /*+ full(c) parallel(c,4) */*
From Table_C Partition(Part_16) c;

ERROR at line 1:
ORA-12801: error signaled in parallel query server P039
ORA-01578: ORACLE data block corrupted (file # 1727, block # 174392)
ORA-01110: data file 1727: ‘/PROD/data70/cpp_calls_73_prod.dbf’

En el alert:

Corrupt Block Found
TSN = 107, TSNAME = CPP_CALLS
RFN = 704, BLK = 174392, RDBA = -1342002888
OBJN = 1797188, OBJD = 1797188, OBJECT = TABLE_C, SUBOBJECT = PART_16
SEGMENT OWNER = STL, SEGMENT TYPE = Table Partition

Consultamos en dba_extents, con el dato de file 1727 y block 174392

Select /*+ rule*/ *
From Dba_Extents
Where File_Id = 1727 /*file*/
And 174392/* block*/ Between Block_Id And Block_Id + Blocks – 1;

OWNER USER
SEGMENT_NAME TABLE_C
PARTITION_NAME PART_16
SEGMENT_TYPE TABLE PARTITION
TABLESPACE_NAME DATOS
EXTENT_ID 99
FILE_ID 1727
BLOCK_ID 174089
BYTES 20971520
BLOCKS 2560
RELATIVE_FNO 704

Marcamos el bloque como corrupto:

SET SERVEROUTPUT ON
Declare
  Num_Corrupt Int;
Begin
  Num_Corrupt := 0;
  Dbms_Repair.Check_Object(Schema_Name       => ‘USER’,
                           Object_Name       => ‘TABLE_C’,
                           Partition_Name    => ‘PART_16’,
                           Repair_Table_Name => ‘REPAIR_TABLE’,
                           Corrupt_Count     => Num_Corrupt);
  Dbms_Output.Put_Line(‘number corrupt: ‘ || To_Char(Num_Corrupt));
End;
/

number corrupt: 1

PL/SQL procedure successfully completed.

Una vez finalizado, podemos consultar la tabla repair_table:

SELECT * From REPAIR_TABLE

OBJECT_ID            1797188
TABLESPACE_ID        107
RELATIVE_FILE_ID     704
BLOCK_ID             174392
CORRUPT_TYPE         6148
SCHEMA_NAME          USER
OBJECT_NAME          TABLE_C
BASEOBJECT_NAME     
PARTITION_NAME       PART_16
CORRUPT_DESCRIPTION 
REPAIR_DESCRIPTION   mark block software corrupt
MARKED_CORRUPT       TRUE
CHECK_TIMESTAMP      18/08/2011 04:33:09 p.m.
FIX_TIMESTAMP
REFORMAT_TIMESTAMP   

Ahora podemos saltar el bloque corrupto con el sgte procedimiento:

Begin
Dbms_Repair.Skip_Corrupt_Blocks(Schema_Name => ‘USER’,
Object_Name => ‘TABLE_C’,
Object_Type => Dbms_Repair.Table_Object,
Flags => Dbms_Repair.Skip_Flag);
End;
/

Si consultamos la tabla:

Select Table_Name, Skip_Corrupt
From Dba_Tables
Where Table_Name = ‘TABLE_C’

TABLE_NAME SKIP_CORRUPT
——————————–
TABLE_C ENABLED

Y finalmente:

Create Table cpp As
Select /*+ full(c) parallel(c,4) */*
From Table_C Partition(Part_16) c;

Table created.
Elapsed: 00:09:42.79