1

Topic: ALTER TABLE source modify lob (FILE_F) (SHRINK SPACE cascade) and ORA-01578

Oracle Enterprise 10g. Version 10.2.0.1.0
Windows 2000 Server
There is table SOURCE. Lives in tablespace AM3 .
In the table there is field FILE_F of type BLOB which lives in tablespace BLOBTBS (more than BLOB-fields in the table is not present).
After removal approximately 40 % of the data in the table remain about 22 million records.
It is necessary to release a place in tablespace AM3 and BLOBTBS!
For place clearing in tablespace I do SHRINK SPACE
1. Made successfully:

ALTER TABLE source SHRINK SPACE

The place in tablespace AM3 was released.
2. At performance:

ALTER TABLE source modify lob (FILE_F) (SHRINK SPACE cascade)

Error: [1]: (Error): ORA-01578: ORACLE data block corrupted (file # 78, block # 2272847) ORA-01110: data file 78: ' V:\ORACLE\BLOBTBS\BLOBFILE.0071'
3.

select SEGMENT_NAME, SEGMENT_TYPE, OWNER
from dba_extents where file_id = 78 and
2272847 between block_id and block_id + blocks-1
SEGMENT_NAME SEGMENT_TYPE OWNER
-------------------------- ------------- ------
SYS_LOB0000052671C00008 $$ LOBSEGMENT ROOT

4. Took from this forum a script, for check of a contained BLOB-field, after its performance - errors it is not found:

DECLARE
N number;
source_file_f blob;
error_1578 exception;
pragma exception_init (error_1578,-1578);
BEGIN
for T1 in (select SOURCE.rowid as source_rowid from SOURCE) LOOP
select SOURCE.file_f into source_file_f from SOURCE where SOURCE.rowid = T1.source_rowid;
begin N: = dbms_lob.instr (source_file_f, hextoraw (' 889911 '));
exception
when error_1578 then
insert into ROWID1 values (T1.source_rowid); commit;
end;
end LOOP;
END;

Actually a question: how to find where there live beaten units and how from them to get rid???
I ask the help where to dig???
Thanks.