Introduction:
The following script is to validate if there are any corrupted rowids in FND_LOBS table
Why we need to do this?
When we run this validation script it will return the corrupted rows, if we get new rowids adding to the removed corrupted rowids we need to perform expdp to remove new rowids
Script to check for corrupted rows,
1)Create a table with corrupt_rowid and err_number
create table CORRUPT_LOBS_FNDLOBS (corrupt_rowid rowid, err_num number);
2)The declare statement will insert the corrupted rowids from FND_LOBS table
declare
error_1578 exception;
error_1555 exception;
error_22922 exception;
pragma exception_init(error_1578,-1578);
pragma exception_init(error_1555,-1555);
pragma exception_init(error_22922,-22922);
n number;
begin
for cursor_lob in (select rowid r, FILE_DATA from APPLSYS.FND_LOBS) loop
begin
n:=dbms_lob.instr(cursor_lob.FILE_DATA,hextoraw(‘889911’));
exception
when error_1578 then
insert into CORRUPT_LOBS_FNDLOBS values (cursor_lob.r, 1578);
commit;
when error_1555 then
insert into CORRUPT_LOBS_FNDLOBS values (cursor_lob.r, 1555);
commit;
when error_22922 then
insert into CORRUPT_LOBS_FNDLOBS values (cursor_lob.r, 22922);
commit;
end;
end loop;
end;
/
3)The below query will return the details of corrupted rowids,
Select rowid,file_id,file_content_type,file_data,upload_date,expiration_date,program_name,program_tag,language,oracle_charset,file_format from apps.fnd_lobs where rowid in(select corrupt_rowid from sys.CORRUPT_LOBS_FNDLOBS15);
