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

数据库环境：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分钟后导出完毕,完美。

因为记忆深刻特此记录。