If some object is being changed when data pump is run how will that object go into datapump file?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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.