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


Komentáře