If some object is being changed when data pump is run how will that object go into datapump file?

  • A session performing a datapump export is the same as any other Oracle database session and consequently "obeys" the same transaction isolation rules. See docs.oracle.com/cd/B13789_01/server.101/b10743/… – Colin 't Hart May 30 '14 at 19:26
  • You might want to edit your question's title to be more specific so other users will find it via search. – Joshua Huber Jun 2 '14 at 15:04

If someone is INSERT/DELETE/UPDATE on a table at the same moment your expdp is working on that table, it won't affect that object. You will get a consistent snap of the table at the moment that expdp got to that table. Basically expdp is consistent WITHIN a table, but not BETWEEN/AMONG tables, unless ...

Since expdp processes your tables serially, they will not be consistent between each other unless you do your expdp with the FLASHBACK_TIME=SYSTIMESTAMP option. I typically do this on every expdp unless I have a good reason not to. This does not require database flashback to be enabled, but makes use of UNDO (rollback segments). If your data changes too rapidly, your expdp will error with "ORA-01555 snapshot too old" and the table will be skipped. This means that there was not enough space in UNDO to preserve read-consistency across your entire expdp job. Increase UNDO retention in this case.

Finally, if someone or some process does DDL on your table (such as TRUNCATE, DROP, or adding/dropping columns/partitions) while expdp is runnning, then the expdp will fail on this object with ORA-01466: unable to read data - table definition has changed. Fortunately this doesn't happen too often, but I have seen it with ETL workloads.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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