- Check the database alert log. If you see messages like Checkpoint Incomplete, or if log files are switching frequently, then you need to increase the size and number of redo log files.
- Set or increase the size of the BUFFER parameter on your import. This will increase the size of the buffer that Oracle uses to do the bulk inserts.
- If possible, create a large UNDO tablespace, and switch to that large UNDO tablespace by using the alter system undo_tablespace=Large_Undo before the import. Then, run the IMP with the COMMIT=N option. After the import completes, switch back to the old UNDO tablespace and then drop the large one if it is no longer necessary. If you do not have the enough space for a larger redo then try increasing the COMMIT parameter setting on the IMP command.
- Increase the SORT_AREA_SIZE parameter. Increasing this parameter will provide more space for sorting operations when creating indexes.
- Increase the LOG_CHECKPOINT_INTERVAL. This will help to reduce the number of checkpoints.
Dig Deeper on Oracle database backup and recovery
Related Q&A from Phillip Bracken
I have a table with 1.5 million records that needs to have a column updated based on a correlated subselect. This update is currently sitting at four... Continue Reading
A user complains that every time he tries to access a table (select only), it takes more than two hours to get the results. There are no DML ... Continue Reading
I have always used TKPROF to do SQL tuning in previous versions of Oracle (7,8 and 9). Can I still use TKPROF in Oracle 10g R2? Continue Reading