Oracle Logbook

June 12, 2012

How to control the speed of archive generation during an Oracle import

Filed under: Uncategorized — Helio Dias @ 5:19 pm

The scenario is an Oracle 10g, importing a table from 35GB dump file inside a test instance, with small archive area and running a simultaneous delete at the same table.(therefore we should use commit=y)

Another huge constraint is that the archive backup run each other hour and can´t be changed.

The bottom line is that I need something like a parameter MAXIMUM_ROWS_IMP_HOUR=2000000 , the issue is that such parameter doesn´t exist.

The solution:

Encompass the lock table, delete , sleep(big), commit and another sleep (small) inside a PL/SQL block

So the “sleep” will give to import some rows and the lock will slow down the pace of the whole process

Blog at