Speeding-Up Oracle Export/Import Migration

Step 1 – export
Step 2 – import data
Step 3 – import everything else

1.Assuming a full export, use direct=y if you’re not using any predicates in the export.
Set your buffer to be big (10MB at least)
2.Alter all your constraints novalidate prior to export if you can bring the app down, and if you can take a consistent export. This helps in reinstating the constraints instantly without forcing Oracle to validate constraints on massive tables
3. set workarea_size_policy=manual
4. Set sort_area_size=6GB (yes, 6 GB). Combined with 4, it let Oracle build indexes with more space in memory for all the sorts without the need to spill to disk.
5. Set massive online redo logs at 2GB each, 2 members each, 6 groups. After the import, reset everything back down to “normal”.

You can locate amount of time remaining to finish an export using

select * from (
select opname, target, sofar, totalwork,
units, elapsed_seconds, totalwork/sofar – 1, round(elapsed_seconds * (totalwork/sofar -1)) Time_Remaining_Seconds, message
from v$session_longops order by start_time desc)
where rownum <=1;

Alternatively use TOAD and click on “Session Browser”

Look at Time Remaining to get a rough estimate of time.