I'm using Oracle 11g databse. For logical backup, we are exporting database on daily basis using below command and dump size is almost 12GB. , but now I'm facing an issue with my expdp.

expdp 'sys/sys#123@Test as sysdba' DUMPFILE=Backup_04042017.dmp LOGFILE=BakupLog_04042017_1603.log FULL=y consistent=y

Export gets completed successfully, but two tables are not getting exported. The following error is shown in the log.

ORA-31693: Table data object "Owner"."Table_1" 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

As read from many blogs, I've set undo_retention value from default to 36000 and set the retention for its corresponding table using below command.

alter table OWNER.TABLE modify lob(DESCRIPTION_HTML_) (retention); 

It doesn't work as expected. So, I've set PCTVERSION to 40% using below command.


My bad, that too didn't work. Also I tried to export the single table which also given me the same error.

Can I set PCTVERSION more than 40%? Does it inversely affect my production database?

Following are the results that I got related to undo size where my optimal undo_retention is showing lower value than undo_retention. Moreover, needed_undo_size is greater than actual_undo_size.

enter image description here


enter image description here

Do I need to increase undo_size? How it should be calculated based on my database configuration?

  • How big is your undo tablespace? Is there much activity during the export? Did you try the DATAPUMP_PARALLEL parameter?
    – Marco
    Apr 4, 2017 at 8:46
  • Caveat: parallel only works for Enterprise Edition. I have a rather small database that I can't export because there are several jobs running contantly. If this is the case, temporalily setting JOB_QUEUE_PROCESSES to zero might work
    – jmk
    Apr 4, 2017 at 9:14
  • Why are you changing PCTVERSION — do you have LOBs in the table? Apr 5, 2017 at 12:41

1 Answer 1

  1. Do I need to increase undo_size? …

    Yes, that is exactly what you need to do. The export needs a consistent MVCC snapshot from the start of the operation. UNDO_RETENTION is ignored if your UNDO tablespace is fixed size or can't be extended:

    UNDO_RETENTION specifies (in seconds) the low threshold value of undo retention. For AUTOEXTEND undo tablespaces, the system retains undo for at least the time specified in this parameter, and automatically tunes the undo retention period to satisfy the undo requirements of the queries. For fixed- size undo tablespaces, the system automatically tunes for the maximum possible undo retention period, based on undo tablespace size and usage history, and ignores UNDO_RETENTION unless retention guarantee is enabled.

  2. … How it [undo size] should be calculated based on my database configuration?

    It needs to be calculated based on how quickly you are generating UNDO (with DML), and how long the export takes — you'll need to find a size that works for you or try and do the export when less UNDO is being generated, or speed up the export. If you have the space available, increasing the size of your UNDO is the easy solution.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.