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.
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
SQL> connect / as sysdba;
SQL> shutdown immediate;
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 123456789 bytes
Fixed Size 123456 bytes
Variable Size 1234567 bytes
Database Buffers 1234567 bytes
Redo Buffers 1234567 bytes
SQL> alter database mount;
SQL> alter database noarchivelog;
SQL> alter database open;
4. Set undo retention to 1 min i.e. 60 seconds
ALTER SYSTEM SET UNDO_RETENTION = 60;
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.