【Oracle】expdp-ora-01555

最新推荐文章于 2024-07-23 08:45:00 发布

戒掉贪嗔痴(薛双奇)

最新推荐文章于 2024-07-23 08:45:00 发布

阅读量623

点赞数 6

分类专栏：数据库运维-Oracle-常规故障文章标签： oracle 服务器数据库

本文链接：https://blog.csdn.net/weixin_43346403/article/details/136356401

版权

数据库运维-Oracle-常规故障专栏收录该内容

90 篇文章 4 订阅

订阅专栏

1.expdp备份报错

Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 13.83 GB
Processing object type SCHEMA_EXPORT/USER
ORA-31693: Table data object "V3XUSER"."WF_PROCESS_RUNNING" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number  with name "" too small
ORA-22924: snapshot too old

2.原因

BLOB或CLOB字段损坏。

3.修复方法

SQL>  create table corrupt_lobs (corrupt_rowid rowid, err_num number);

Table created.

SQL> 

SQL> desc "V3XUSER"."WF_PROCESS_RUNNING";
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID					   NOT NULL VARCHAR2(110)
 TEMPLETE_ID					    NUMBER(38)
 PROCESSOBJECT					    BLOB
 CREATEDATE					    NUMBER(38)
 STATE						    NUMBER(4)


SQL> 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);
   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_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 table_owner: V3XUSER
Enter value for table_with_lob: WF_PROCESS_RUNNING
old  11: 		select rowid r, &&lob_column from &table_owner..&table_with_lob)
new  11: 		select rowid r, PROCESSOBJECT from V3XUSER.WF_PROCESS_RUNNING)
old  14: 		num := dbms_lob.instr (cursor_lob.&&lob_column, hextoraw ('889911')) ;
new  14: 		num := dbms_lob.instr (cursor_lob.PROCESSOBJECT, hextoraw ('889911')) ;

PL/SQL procedure successfully completed.

SQL> select * from corrupt_lobs;

CORRUPT_ROWID	      ERR_NUM
------------------ ----------
AAAVw2AARAAATNvAAM	 1555

update V3XUSER.WF_PROCESS_RUNNING
set PROCESSOBJECT = empty_blob()
where rowid in (select corrupt_rowid from corrupt_lobs);
commit;

select * from V3XUSER.WF_PROCESS_RUNNING where rowid='AAAVw2AARAAATNvAAM';

ID					 TEMPLETE_ID  PROCESSOBJ CREATEDATE	STATE
-------------------- ------------ ------    ----------- ---------- 
-1518117889596177419						1.6291E+12	    0

4.总结

EXPDP 执行时报错:ORA-01555,常见问题就是BLOB或CLOB字段损坏。