错误,如题所示,具体报错如下:

看看上面出问题的数据表的详情:

对于该报错,Oracle Support,也有说明:
ORA-01555 And Other Errors while Exporting Table With LOBs, How To Detect Lob Corruption. (文档 ID 452341.1)

确认到底是那些BLOB有问题:

Code:
————————————
set serverout on
exec dbms_output.enable(100000);
declare
page number;
len number;
c varchar2(10);
charpp number := 8132/2;

begin
for r in (select rowid rid, dbms_lob.getlength () len
from ) loop
if r.len is not null then
for page in 0..r.len/charpp loop
begin
select dbms_lob.substr (, 1, 1+ (page * charpp))
into c
from
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;
/
————————————
创建一个表存放出错的对象的ROWID信息:

根据上面的ROWID,去看看能不能导出:

换一种方式试试:

很显然是不行的。

修复BLOB带来的问题:

再次查看:

问题已经得到修正。

另外,上面为了查找某个USER下有哪些BLOB,我写了一个SQL:
set pagesize 45;
col table_name for a24
col column_name for a24
col owner for a12
select owner|| ‘.’ ||table_name|| ‘.’ ||column_name from dba_tab_columns where owner in (‘VPX’,’VCLOUD’) and DATA_TYPE=’BLOB’;
————————————
Done。

6 thoughts on “Oracle:ORA-01555与BLOB”

  1. LOBs and ORA-01555 troubleshooting (文档 ID 846079.1)

    Troubleshooting Assistant: Resolve Issues with Oracle Undo Management (ORA-01555, ORA-30036, ORA-01628, ORA-01552, etc.) (文档 ID 1575667.2)

    Export Fails With Errors ORA-2354 ORA-1555 ORA-22924 And How To Confirm LOB Segment Corruption Using Export Utility (文档 ID 833635.1)

    ORA-1578 ORA-26040 in a LOB segment – Script to solve the errors (文档 ID 293515.1)

  2. set concat off

    declare
    error_1555 exception;
    pragma exception_init(error_1555,-1555);
    num 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_1555 then
    insert into corrupted_lob_data values (cursor_lob.r);
    commit;
    end;
    end loop;
    end;

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

隐藏
变装