Oracle Data Pump Export (expdp) locks table (or something similar)
I must export data from a partitioned table with global index that must be online all the time, but I am having troubles in doing that. For data export I am using Data Pump Export - expdp and I am exporting only one partition. The oldest one, not the active one.
My expdp command exports correct data and it looks like this:
expdp user/pass@SID DIRECTORY=EXP_DIR
DUMPFILE=part23.dmp TABLES=SCHEMA_NAME.TABLE_NAME:TABLE_PARTITION_23`
Application that uses database has a connection timeout of 10 seconds. This parameter can't be changed. If INSERT queries are not finished within 10 seconds, data is written to a backup file.
My problem is that, during the export process that lasts few minutes, some data ends up in the backup file, and not in the database. I want to know why, and avoid it.
Partitions are organized weekly, and I am keeping 4 partitions active (last 4 weeks). Every partition is up to 3 GB.
I am using Oracle 11.2
Answer
Are you licensed to use the AWR? If so, do you have an AWR report for the snapshot when the timeouts occurred?
Oracle readers don't block writers and there would be no reason for an export process to lock anything that would impact new inserts.
Is this a single INSERT operation that has a timeout of 10 seconds (i.e. you are inserting a large number of rows in a single INSERT statement)? Or is this a batch of individual inserts such that some of the inserts can succeed in the 10 second window and some can fail? You say that "some data ends up in the backup file" but I'm not sure which of these scenarios are more accurate.
During normal operations, how close are you to the 10 second time-out?
Is it possible that the system is I/O bound and that doing the export increases the load on the I/O system causing all operations to be slower? If you've got an I/O bottleneck and you add an export process that has to read a 3 GB partition and write that data to disk (presumably also on the database server), that could certainly cause a general slowdown. If you're reasonably close to the 10 second time-out already, that could certainly push you over the edge.