SQL> SELECT SEGMENT_TYPE,OWNER,SEGMENT_NAME FROM DBA_EXTENTS WHERE FILE_ID = 51 AND 133824 BETWEEN BLOCK_ID AND BLOCK_ID+BLOCKS-1;

SEGMENT_TYPE	   OWNER							SEGMENT_NAME
------------------ ------------------------------   --------------------------
LOBSEGMENT	   		SAPSR3							SYS_LOB0000055255C00005$$

SQL> execute dbms_repair.skip_corrupt_blocks('SAPSR3', 'DYNPSOURCE');

PL/SQL procedure successfully completed.

SQL> SELECT OWNER, TABLE_NAME, SKIP_CORRUPT FROM DBA_TABLES WHERE TABLE_NAME ='DYNPSOURCE';

OWNER			       TABLE_NAME		      SKIP_COR
------------------------------ ------------------------------ --------
SAPSR3			       DYNPSOURCE		      ENABLED

SQL> create table DYNPSOURCE_corrupt AS SELECT * FROM SAPSR3.DYNPSOURCE;
create table DYNPSOURCE_corrupt AS SELECT * FROM SAPSR3.DYNPSOURCE
                                                        *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 51, block # 133824)
ORA-01110: data file 51: '/oracle/PRD/sapdata3/sr3700_11/sr3700.data11'

# cp /oracle/PRD/sapdata3/sr3700_11/sr3700.data11 /tmp
#

SQL> select BANNER from V$VERSION;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE	10.2.0.4.0	Production
TNS for HPUX: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> select owner, segment_name, segment_type 
  2  from   dba_extents
  3  where  file_id = 51
  4  and 133824 between block_id and block_id + blocks-1;  

OWNER
------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
SEGMENT_TYPE
------------------
SAPSR3
SYS_LOB0000055255C00005$$
LOBSEGMENT


SQL> select table_name, column_name
  2  from   dba_lobs
  3  where  segment_name = 'SYS_LOB0000055255C00005$$'
  4  and    owner = 'SAPSR3';

TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
DYNPSOURCE
LOGICINFO


SQL> select table_name 
  2  from dba_lobs 
  3  where segment_name = 'SYS_LOB0000055255C00005$$'
  4  and owner = 'SAPSR3'; 

TABLE_NAME
------------------------------
DYNPSOURCE


SQL> describe SAPSR3.DYNPSOURCE;
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 PROGNAME				   NOT NULL VARCHAR2(120)
 DYNPNUMBER				   NOT NULL VARCHAR2(12)
 R3STATE				   NOT NULL VARCHAR2(3)
 FIELDINFO					    BLOB
 LOGICINFO					    BLOB
 EXTENSIONS					    BLOB

SQL> create table corrupted_data (corrupted_rowid rowid);

set concat off
declare
  error_1578 exception;
  pragma exception_init(error_1578,-1578);
  n number;
begin
   for cursor_lob in (select rowid r, &&lob_column from &&table_owner.&table_with_lob) loop
   begin
     n:=dbms_lob.instr(cursor_lob.&&lob_column,hextoraw('889911')) ;
   exception
     when error_1578 then
       insert into corrupted_data values (cursor_lob.r);
       commit;
   end;
  end loop;
end;
/ 
Table created.

На вопросы вводим:

Enter value for lob_column: LOGICINFO
Enter value for table_owner: SAPSR3
Enter value for table_with_lob: DYNPSOURCE
old   6:    for cursor_lob in (select rowid r, &&lob_column from &&table_owner.&table_with_lob) loop
new   6:    for cursor_lob in (select rowid r, LOGICINFO from SAPSR3.DYNPSOURCE) loop
old   8:      n:=dbms_lob.instr(cursor_lob.&&lob_column,hextoraw('889911')) ;
new   8:      n:=dbms_lob.instr(cursor_lob.LOGICINFO,hextoraw('889911')) ;


PL/SQL procedure successfully completed.


SQL> set concat off
SQL> update &table_owner.&table_with_lob 
  2  set &lob_column = empty_blob() 
  3  where rowid in (select corrupted_rowid from corrupted_data);
Enter value for table_with_lob: DYNPSOURCE
old   1: update &table_owner.&table_with_lob
new   1: update SAPSR3.DYNPSOURCE
old   2: set &lob_column = empty_blob()
new   2: set LOGICINFO = empty_blob()

1 row updated.

SQL> commit;
Commit complete.