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: : (Error): ORA-01578: ORACLE data block corrupted (file # 78, block # 2272847) ORA-01110: data file 78: ' V:\ORACLE\BLOBTBS\BLOBFILE.0071'
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???