Data Pump: Consistent Export?

Ouch … I have to admit as I did say in several workshops in the past weeks that a data pump export with expdp is per se consistent.

Well … I thought it is … but it’s not. Thanks to a customer who is doing a large unicode migration at the moment. We were discussing parameters in the expdp’s par file. And I did ask my colleagues after doing some research on MOS. And here are the results of my “research”:

  • MOS Note 377218.1 has a nice example showing a data pump export of a partitioned table with DELETEs on that table as inconsistent
  • Background:
    Back in the old 9i days when Data Pump was designed flashback technology wasn’t as popular and well known as today – and UNDO usage was the major concern as a consistent per default export would have heavily relied on UNDO. That’s why – similar to good ol’ exp – the export won’t operate per default in consistency mode
  • To get a consistent data pump export with expdp you’ll have to set:
    FLASHBACK_TIME=SYSTIMESTAMP
    in your parameter file. Then it will be consistent according to the timestamp when the process has been started. You could use FLASHBACK_SCN instead and determine the SCN beforehand if you’d like to be exact.

So sorry if I had proclaimed a feature which unfortunately is not there by default 🙁

– Mike

2 thoughts on “Data Pump: Consistent Export?

  1. Thanks for that input – but that CONSISTENT=Y got introduced with the Legacy interface to be backwards compatible for pre-datapump parameter files – and it will translate to the flashback_scn internally 😉

    But to simplify typing you are right – so thanks again!

    Mike

Leave a Reply

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