2

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

casperOne
  • 73,706
  • 19
  • 184
  • 253
Ivica
  • 63
  • 1
  • 2
  • 5

1 Answers1

1

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.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • I will be able to check if we have the AWR license on Monday. But I believe that it is not available on the development system where I am testing the export procedure. Application makes batch of individual inserts. I can't change the application logic. – Ivica Jul 01 '11 at 20:39
  • @lvica - If you don't have AWR available, can you see if you have Statspack available? – Justin Cave Jul 01 '11 at 20:42
  • During normal operations INSERT transaction is finished in less then 1 second. Timeout is set to 10 seconds because of some SELECT statements joined over several tables. So far I didn't notice any problems on the I/O system. I will check also the behavior of the I/O system during the export on Monday. Thank for the fast response – Ivica Jul 01 '11 at 20:52
  • The database installation doesn't have an AWR license, and Statspack is not installed. I will try to install it as soon as possible. – Ivica Jul 04 '11 at 06:26