ORA-22924: snapshot too old

I got ” ORA-22924: snapshot too old ”  error in Oracle database.


ORA-22924: snapshot too old


Details of error are as follows.

When exporting tables having LOB columns, using data pump or conventional export the
 following errors might occur:


ORA-31693: Table data object "<SCHEMA_NAME>"."<TABLE_NAME>" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEPOPULATE callout
ORA-01555: snapshot too old: rollback segment number with name "" too small
ORA-22924: snapshot too old


. . exporting table <TABLE_NAME>
EXP-00056: ORACLE error 1555 encountered
ORA-01555: snapshot too old: rollback segment number with name "" too small
ORA-22924: snapshot too old


EXP-00056: ORACLE error 22922 found
ORA-22922: nonexistent LOB value
. . Export der Tabelle <TABLE_NAME> 2 rows exported


snapshot too old

This ORA-22924 is related to the corruption in LOB segment. This can also occur due to the fact that LOB PCTVERSION or RETENTION are low. The proposed solution below should not be used on ORA-1555, if:


Read the following post which is related with the Snapshot too old error.




1. You can’t confirm that no updates were made to that LOB while you ran the script


2. You don’t have confirmation that exactly the identical ROWIDs are reported by 2 independent script executions.

Otherwise, all the above errors are produced when exporting, because the LOBs stored in the table to be exported might be corrupted.

To have this checked a PLSQL block should be run against the table.

As there is already a PLSQL procedure indicated in various notes such as: Note 452341.1 or Note 253131.1, the reason for this article is to propose a PLSQL block which will run faster and consumes much less memory.

The PLSQL indicated in the previous articles cannot be run against large tables due to the fact that they can fail with memory errors like an ORA-04031 error.


To verify for corruption use the PLSQL block below. It is built to not consume much of the system resources and to run faster on large tables.


Note: Replace <TABLE_NAME> with the name of the table that has the LOB column (<LOB_COLUMN>) where the corruption is suspected:


-- 1. Create a new temporary table for storing all rowids of the corrupted LOBs. Let's call it "corrupt_lobs"

SQL> create table corrupt_lobs (corrupt_rowid rowid, err_num number);

-- 2. Make a desc on the table containing the LOB column:


Name         Null?     Type
----------   --------- ------------
<LOB_COLUMN>           BLOB

-- Run the following PLSQL block:

  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;
  for cursor_lob in (select rowid r, <LOB_COLUMN> from <TABLE_NAME>) loop
    when error_1578 then
      insert into corrupt_lobs values (cursor_lob.r, 1578);
    when error_1555 then
      insert into corrupt_lobs values (cursor_lob.r, 1555);
    when error_22922 then
      insert into corrupt_lobs values (cursor_lob.r, 22922);
  end loop;

-- In the end all rowids of the corrupted LOBs will be inserted into the corrupt_lobs newly created table.

-- A possible solution would then be to empty the affected LOBs using a statement like:

SQL> update <TABLE_NAME> set <LOB_COLUMN> = empty_blob()
     where rowid in (select corrupt_rowid from corrupt_lobs);

( for BLOB and BFILE columns use EMPTY_BLOB; for CLOB and NCLOB columns use EMPTY_CLOB )

-- Or export the table without the corrupted row, like:

% expdp system/<PASSWORD> DIRECTORY=my_dir DUMPFILE=<dump_name>.dmp LOGFILE=<logfile_name>.log TABLES=<SCHEMA_NAME>.<TABLE_NAME> QUERY=\"WHERE rowid NOT IN \(\'<corrupt_rowid>\'\)\"


Read the following post.

ora-01555: snapshot too old: rollback segment number




Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )


About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply

Your email address will not be published. Required fields are marked *