Oracle Logbook

June 12, 2012

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

Filed under: Uncategorized — heliodias @ 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

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: