ITPub博客

首页 > 数据库 > Oracle > ORACLE 数据导出LOB字段报错ORA-31693,ORA-02354,ORA-22924

ORACLE 数据导出LOB字段报错ORA-31693,ORA-02354,ORA-22924

原创 Oracle 作者:dmcatding 时间:2020-07-13 12:43:10 0 删除 编辑

数据库环境:11.2.0.4

操作系统:redhat linux 6

数据库针对单表导出报错:


表字段包含CLOB字段类型,约6G,整个表8G多


数据库参数设置检查:



以上设置完毕,导出依然一样的报错,最后参照百度的MOS的一篇文章:Doc ID 833635.1

原因为LOB段损坏,使用如下方法来定位损坏的LOB值所在记录的rowid


SQL> create table corrupted_lob_data (corrupted_rowid rowid);


Table created.


SQL> set concat off

SQL> 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;

/  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16  

Enter value for lob_column: CONTENTDATA

Enter value for table_owner: ECLYS

Enter value for table_with_lob: CS_RC_NST

old   6:   for cursor_lob in (select rowid r, &&lob_column from &table_owner.&table_with_lob) loop

new   6:   for cursor_lob in (select rowid r, CONTENTDATA from ECLYS.CS_RC_NST) loop

old   8:       num := dbms_lob.instr (cursor_lob.&&lob_column, hextoraw ('889911')) ;

new   8:       num := dbms_lob.instr (cursor_lob.CONTENTDATA, hextoraw ('889911')) ;


PL/SQL procedure successfully completed.


SQL> select count(*) from corrupted_lob_data;


  COUNT(*)

----------

3


SQL> desc corrupted_lob_data

 Name    Null?    Type

 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------

 CORRUPTED_ROWID     ROWID


SQL> select * from corrupted_lob_data;


CORRUPTED_ROWID

------------------

AAAa2GAAEAAHczDAAZ

AAAa2GAAEAAJE+lAAc

AAAa2GAAEAAJFDSAAD



执行完以后,根据表中存储的rowid值到出错的表中查出对应的记录。如果使用PL/SQL Developer,能看到对应的LOB字段值显示value error。


联系应用手工备份了下这三条数据后,删除,然后重新导出,


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

Starting "SYS"."SYS_EXPORT_TABLE_01":  "/******** AS SYSDBA" directory=pumpdir tables=ECLYS.CS_RC_NST dumpfile=CS_RC_NST0711.dmp logfile=CS_RC_NST0711.log 

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 8.643 GB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/COMMENT

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "ECLYS"."CS_RC_NST"                         6.127 GB  724649 rows

Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYS.SYS_EXPORT_TABLE_01 is:

  /home/oracle/pumpdir/CS_RC_NST0711.dmp

Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Sat Jul 11 10:42:37 2020 elapsed 0 00:45:04


45分钟后导出完毕,完美。

因为记忆深刻特此记录。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30327022/viewspace-2704097/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
此BLOG仅作为个人笔记记录,如果有误请大家指正,谢谢。

注册时间:2015-07-04

  • 博文量
    74
  • 访问量
    141232