Large Volume Data Delete and Insert approach

by Snehashish Ghosh

 Scenario


During Delete or Insert of Large volume of data huge amount of archive log get generated filling up the archive log destination area.
Also large amount of undo records fill up the undo tablespace.
So how should we plan a large volume of data delete or insert in database. Data Volume >20GB can follow this approach.

Solution


1. Increase the DATA tablespace size in case of insert
2. Increase the size of undo tablespace proportional to the amount of data to be deleted or inserted
3. Move the database to noarchivelog mode

4. Set undo retention to 1 min i.e. 60 seconds

CONCLUSION


Delete or Insert of large volume of data requires proper planning.
If possible set the database to noarchivelog mode to stop generation of archivelog files filling up archive destination.
Also reset the undo_retention to as low as possible preferably 1 min or 1 sec during the time of delete or insert operation.
Pre planning this activity would save wastage of time at the time of actual insert or delete operation.

 

 

Leave a reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

*