Re: Snapshot too old from READ-ONLY table (data pump export)

  • From: Saibabu Devabhaktuni <saibabu_d@xxxxxxxxx>
  • To: free <oracle-l@xxxxxxxxxxxxx>
  • Date: Sat, 16 Jul 2011 12:03:45 -0700 (PDT)

Which version of Oracle are you using?

When the tablespace is in read only mode, delayed block cleanout is performed 
but the dirty buffers will not be written to disk. Same is not true for read 
only tables.
More on this 

If you were to select the records from the block you got ORA-1555, by using 
rowid predicate instead, you wouldn't have gotten ORA-1555. Oracle is trying to 
update ITL in block header with the minimum SCN of all the transactions 
available in the undo segments, since your export datapump is running longer 
than the undo retention, you are getting this error. Try running export 
datapump with parallel threads.

There is a concept of "min active scn" introduced in 11g which is supposed to 
enhance delayed block cleanout operations.

But in reality, Oracle shouldn't even need to do the delayed block cleanout on 
*sufficiently* old blocks (determined by min active scn - which is supposed to 
be minimum scn of all active transactions). I opened enhancement request on 
this last year, Oracle support opened bug# 9941362 for this issue (hopefully 
they fix it in 12g).


Other related posts: