Problém:
Není možné vyexportovat tabulku ORA-31693: Objekt dat tabulky "DBA"."LOB_TABLE" selhal pri nacteni/zruseni nacteni a je preskocen z duvodu chyby: ORA-02354: chyba pri exportu/importu dat ORA-01555: prilis stary snimek: segment rollback cislo jmeno "" prilis maly ORA-22924: snimek je prilis stary Pro tabulku master "DBA"."SYS_EXPORT_TABLE_01" bylo uspesne provedeno nacteni/zruseni nacteni
Analýza:
Export Receives The Errors ORA-1555 ORA-22924 ORA-1578 ORA-22922 (Doc ID 787004.1)
SYS@ORCL> create table corrupt_lobs (corrupt_rowid rowid, err_num number); Tabulka vytvorena. declare error_1578 exception; error_1555 exception; error_22922 exception; pragma exception_init(error_1578,-1578); pragma exception_init(error_1555,-1555); pragma exception_init(error_22922,-22922); n number; begin for cursor_lob in (select rowid r, VALUE from DBA.LOB_TABLE) loop begin n:=dbms_lob.instr(cursor_lob.VALUE,hextoraw('889911')); exception when error_1578 then insert into corrupt_lobs values (cursor_lob.r, 1578); commit; when error_1555 then insert into corrupt_lobs values (cursor_lob.r, 1555); commit; when error_22922 then insert into corrupt_lobs values (cursor_lob.r, 22922); commit; end; end loop; end; / Procedura PL/SQL uspesne dokoncena. SYS@ORCL> select corrupt_rowid from corrupt_lobs; CORRUPT_ROWID ------------------ AAA7DxAAbAAN8pMAAY AAA7DxAAbAAN8pMAAZ AAA7DxAAbAAN8pMAAa AAA7DxAAbAAN8pMAAb AAA7DxAAbAAN8pMAAc SYS@ORCL> SYS@ORCL> select count(*) from DBA.LOB_TABLE; COUNT(*) ---------- 31775
Řešení
expdp DBA@ORCL.WORLD tables=LOB_TABLE PARALLEL=1 directory=exp_dir dumpfile=LOB_TABLE.dmp logfile=LOB_TABLE_6.log flashback_time=systimestamp QUERY=\"WHERE rowid NOT IN \(\'AAA7DxAAbAAN8pMAAY\',\'AAA7DxAAbAAN8pMAAZ\',\'AAA7DxAAbAAN8pMAAa\',\'AAA7DxAAbAAN8pMAAb\',\'AAA7DxAAbAAN8pMAAc\'\)\" Export: Release 11.2.0.4.0 - Production on Thu May 4 10:26:31 2017 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Password: Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production Probiha spusteni "DBA"."SYS_EXPORT_TABLE_01": DBA/********@ORCL.WORLD tables=LOB_TABLE PARALLEL=1 directory=exp_dir dumpfile=LOB_TABLE.dmp logfile=LOB_TABLE_6.log flashback_time=systimestamp QUERY="WHERE rowid NOT IN ('AAA7DxAAbAAN8pMAAY','AAA7DxAAbAAN8pMAAZ','AAA7DxAAbAAN8pMAAa','AAA7DxAAbAAN8pMAAb','AAA7DxAAbAAN8pMAAc')" Probiha odhad za pouziti metody BLOCKS ... Probiha zpracovani typu objektu TABLE_EXPORT/TABLE/TABLE_DATA Celkovy odhad za pouziti metody BLOCKS: 11.77 GB Probiha zpracovani typu objektu TABLE_EXPORT/TABLE/TABLE Probiha zpracovani typu objektu TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Probiha zpracovani typu objektu TABLE_EXPORT/TABLE/INDEX/INDEX Probiha zpracovani typu objektu TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Probiha zpracovani typu objektu TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Probiha zpracovani typu objektu TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT . . exportovano "DBA"."LOB_TABLE" 11.48 GB 31770 radku Pro tabulku master "DBA"."SYS_EXPORT_TABLE_01" bylo uspesne provedeno nacteni/zruseni nacteni ****************************************************************************** Soubor dump nastaveny pro DBA.SYS_EXPORT_TABLE_01 je: /exportdb/export/LOB_TABLE.dmp Uloha "DBA"."SYS_EXPORT_TABLE_01" byla uspesne dokoncena na Ct Kve 4 10:31:50 2017 elapsed 0 00:05:16