LOB segment corruption

今天用expdp導數據出現ORA-01555: snapshot too old 錯誤，表中有clob字段，增加retention time和增加undo表空間都沒有用，最后發現是有LOB segment corruption。

ORA-31693: Table data object "NEWCMS"."ARTICLE" 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

create table corrupted_data (corrupted_rowid rowid);
 

檢查壞塊
set concat off
declare
  error_1555 exception;
  pragma exception_init(error_1555,-1555);
  v_lob NCLOB;
  n number;
begin
   for cursor_lob in (select rowid r  from article.html_context) loop
   begin
     select  PT_DESC into v_lob  from   article.html_context  where rowid=cursor_lob.r;
     n:=dbms_lob.instr(v_lob,hextoraw('889911')) ;
   exception
     when error_1555 then
       insert into corrupted_data values (cursor_lob.r);
       commit;
   end;
  end loop;
end;
/ 

PL/SQL procedure successfully completed.

SQL> select * from corrupted_data;

CORRUPTED_ROWID

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

AAAS6HAAKAAB++3AAA

發現有一條數據損壞

導出時過濾掉該條記錄

expdp newcms/********* schemas=newcms dumpfile=newcms_20160509.dmp query=\"where rowid not in \(\'AAAS6HAAKAAB++3AAA\'\)\" version=10.2.0.4.0

成功導出

在導入時報錯

ORA-39083: Object type REF_CONSTRAINT failed to create with error:

ORA-02298: cannot validate (NEWCMS.FK_ARTICLE__REFERENCE_ARTICLE) - parent keys not found

Failing sql is:

ALTER TABLE "NEWCMS"."ARTICLE_TO_COLUMN" ADD CONSTRAINT "FK_ARTICLE__REFERENCE_ARTICLE" FOREIGN KEY ("ARTICLE_CODE") REFERENCES "NEWCMS"."ARTICLE" ("ID") ENABLE

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "NEWCMS"."SYS_IMPORT_FULL_01" completed with 2 error(s) at 14:45:36

原因:

你要插入的表A里，有外鍵連接到另一個表B的主鍵， 你在表A的外鍵列插入的值 在表B的主鍵列找不到就不能插入。

解決方法:

如果可以刪除主表中的多余記錄，保證主表和子表一致

下面的語句根據索引關聯信息表生成刪除語句

SELECT ' delete from '

 ||a.table_name

 ||' a  where not exists ( select 1 from '

 ||c_pk.table_name

 || ' b  where b.'

 || b.column_name

 ||'=a.'

 ||a.column_name

 ||');'

FROM user_cons_columns a

JOIN user_constraints c

ON a.constraint_name = c.constraint_name

JOIN user_constraints c_pk

ON c.r_constraint_name = c_pk.constraint_name

JOIN user_cons_columns b

ON c_pk.constraint_name = b.constraint_name

WHERE c.constraint_type = 'R'

AND a.table_name        = '&Table_Name'

AND a.constraint_name   ='&FK_NAME';

'DELETEFROM'||A.TABLE_NAME||'AWHERENOTEXISTS(SELECT1FROM'||C_PK.TABLE_NAME||'BWH

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

 delete from ARTICLE_TO_COLUMN a  where not exists ( select 1 from ARTICLE b  wh

ere b.ID=a.ARTICLE_CODE);

執行該語句

然后執行

alter table "ARTICLE_TO_COLUMN" enable constraint "FK_ARTICLE__REFERENCE_ARTICLE"; 

成功！