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分鐘後匯出完畢,完美。

因為記憶深刻特此記錄。