ora-01555（快照过旧）

查看exp备份报错

EXP-00008: ORACLE error 1555 encountered
ORA-01555: snapshot too old: rollback segment number 7 with name "_SYSSMU7$" too small
EXP-00000: Export terminated unsuccessfully

 

现考虑增加快照时间undo_retention

查看undo_retention值为10800－－3个小时

但是还是出错

所以考虑修改成5个小时

 

SQL> ALTER SYSTEM SET undo_retention=18000 SCOPE=BOTH;

系统已更改。

 

 

如果不是时间问题

就去增加undo空间

ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/MYTEST/datafile/undotbs1.dbf' RESIZE 3G

 

 

 

 

 

最近又遇到一个新的问题问题，含有blob字段的报错

一开始以为是以上两种问题

但是修改后没有变化

手动执行很快就过去了，不可能是以上两种状况造成

错误讯息

. . exporting table                  BIZDOCCONTENT
EXP-00056: ORACLE error 1555 encountered
ORA-01555: snapshot too old: rollback segment number  with name "" too small
ORA-22924: snapshot too old

 

网上搜索过后发现这个可能是blob字段损毁的原因，找个脚本提取损毁字段

create table corrupt_lobs (corrupt_rowid rowid);

 

create table corrupt_lobs (corrupt_rowid rowid);
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, CONTENT from WM_IS.BIZDOCCONTENT )
            loop
                begin n:=dbms_lob.instr(cursor_lob.CONTENT,hextoraw('889911'));
                exception when error_1578 then insert into corrupt_lobs values (cursor_lob.r); commit;
                when error_1555 then insert into corrupt_lobs values (cursor_lob.r); commit;
                when error_22922 then insert into corrupt_lobs values (cursor_lob.r);
                commit;
                end;
            end loop;
   end;

 

select * from corrupt_lobs;

 

这些rowid拿到错误表查看即会发现在blob字段上现实<value error>字样

然后就是处理这些数据

 update wm_is.bizdoccontent set CONTENT = empty_blob() where rowid in (select corrupt_rowid from corrupt_lobs);

或

delete from wm_is.bizdoccontent where rowid in (select corrupt_rowid from corrupt_lobs);

 

完毕，再exp就不报错了

 

再转个脚本（没测试过）

set serveroutput on
exec dbms_output.enable(100000);
declare
  pag    number;
  len    number;
  c      varchar2(10);
  charpp number := 8132/2;

begin
  for r in (select rowid rid, dbms_lob.getlength (ANNEX_CONTENT) len
            from  annex ) loop
    if r.len is not null then
      for page in 0..r.len/charpp  loop
        begin
          select dbms_lob.substr (ANNEX_CONTENT, 1, 1+ (page * charpp))
          into   c
          from   annex
          where  rowid = r.rid;
       
        exception
          when others then
            dbms_output.put_line ('Error on rowid ' ||R.rid||' page '||page);
            dbms_output.put_line (sqlerrm);
        end;
      end loop;
    end if;
  end loop;
end;