I'm trying to export a customer's production database (schema) for problem solving.

During export, I'm getting the following:

Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 97.83 GB
Processing object type SCHEMA_EXPORT/USER
ORA-31693: Table data object "SCHEMA"."PROBLEMTABLE" 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 1 with name "_SYSSMU1_1522693822$" too small

The table is just over 5GB (not the largest) but is heavily used 24x7.

Undo retention is set at 900s and, as this is an Amazon RDS instance, I cannot make changes to the parameter option group.

I've tried a number of different options during export - trying to force CONSISTENT=N for example but that didn't work.

The table does not need to be consistent to a specific time, as long as the data contained is approximately similar to the time the rest of the schema was exported then that's acceptable.

Is there another way to handle this in the export command, or would this be better if I exported that specific table first (there are no constraints/dependencies on this object), and then ran the schema export again excluding the table in question?

  • Batch your export?
    – Vérace
    Oct 25, 2021 at 21:48
  • When you say Batch, do you mean break it up into pieces (so problematic table first, followed by the rest in a separate execution)?
    – Huskie69
    Oct 26, 2021 at 8:08
  • yes, break the job up into manageable chunks so that the ORA-01555 can't occur!
    – Vérace
    Oct 26, 2021 at 8:36
  • I've tried exporting just that specific table but still run into the ORA-01555 error
    – Huskie69
    Oct 28, 2021 at 11:42
  • OK then - split it in two? Or maybe investigate your settings? What triggers ORA-01555? Can you temporarily disable logging or whatever causes it? Since all you're doing is a copy and not an any change of data, I think you should probably be able to reduce any transactional overhead using a/some setting(s)?
    – Vérace
    Oct 28, 2021 at 11:48


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.