Shulou(Shulou.com)06/01 Report--

Today, there is an ORA-01555: snapshot too old error using expdp to import data, there are clob fields in the table, adding retention time and undo tablespaces are useless, and it turns out that there is 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)

Check for bad blocks

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 (vault 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

A piece of data was found to be corrupt.

Filter out the record on export

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

Successfully exported

It was wrong to import the Times.

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

Reason:

In the table A you want to insert, there is a foreign key connected to the primary key of another table B. the value you insert in the foreign key column of table A cannot be inserted if it cannot be found in the primary key column of table B.

Solution:

If you can delete the redundant records in the main table, make sure that the main table and child table are consistent

The following statement generates a delete statement based on the index association information table

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)

Execute the statement

And then execute

Alter table "ARTICLE_TO_COLUMN" enable constraint "FK_ARTICLE__REFERENCE_ARTICLE"

Success!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.