current position:Home>Ora-01555 snapshot is too old. Fallback segment number (name) is too small. Ora-22924 snapshot is too old

Ora-01555 snapshot is too old. Fallback segment number (name) is too small. Ora-22924 snapshot is too old

2022-02-02 19:24:38 CSDN Q & A

Two reasons :
1.undo Table space is too small ,undo Retention time is too short
2. The read data has bad blocks
Solution :
1. The first question , enlarge undo Table space and undo Retention time
2. The second way is to solve the problem of bad blocks :
(1) It is preferred to create a table that handles bad blocks
create table corrupt_lobs (corrupt_rowid rowid, err_num number);
(2) Perform the following procedure
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, &&lob_column from &table_owner.&table_with_lob) loop
    begin
      num := dbms_lob.instr (cursor_lob.&&lob_column, hextoraw ('889911')) ;
exception
when error_1578 then
insert into corrupt_lobs values (cursor_lob.r, 1578);
commit;
when error_1555 then
insert into corrupt_lobs values (cursor_lob.r, 1555);
commit;
when error_22922 then
insert into corrupt_lobs values (cursor_lob.r, 22922);
commit;
end;
end loop;
end;
/


Enter value for lob_column    : lob Field name  
Enter value for table_owner   : Users
Enter value for table_with_LOB: lob Table where the field is located

According to the bad blocks found , Update bad block to empty :
update <owner>.<tablename> 
set    <lob column> = empty_blob()
where  rowid in (select corrupted_rowid from corrupted_lob_data);
commit;


remarks : If there are multiple fields, repeat the above program code

copyright notice
author[CSDN Q & A],Please bring the original link to reprint, thank you.
https://en.primo.wiki/2022/02/202202021924355723.html

Random recommended