Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
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 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 at 15:04

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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