Oracle Export & Import Performance Improvements

Index Creation in Parallel

In Oracle version 9i, the Oracle import utility runs single threaded which means that you cannot take advantage of parallel processing. It is possible to import just the data through Oracle’s import utility and then create the indexes in parallel afterwards.

You can specify a file for the index creation statements to go into, using the parameter INDEXFILE. You can see more on this in the Oracle documentation.

Now, when running these create index statements you can either create them all with the NOLOGGING PARALLEL clauses or you can split the index creation statements into multiple files and run them at the same time. If you decide to use the NOLOGGING and PARALLEL clauses, don’t forget to turn them off afterwards…

Using and Export File Before it’s Extracted

Usually, when an Oracle export file is large it will be compressed in some way or another. So when you get it to the destination server where you want to run the import you need to extract it before you can kick off the import, right? Well, kind of…Yes, you need to extract it but you can start the import before the file has finished extracting because the data is all in alphabetical order! That means you can speed up your Oracle import by starting it sooner.


When running an Oracle export, it can be very beneficial to use the parameter DIRECT=Y. This method of Oracle export bypasses the SQL buffer and reads the data directly from disk. Learn about that directly from the Oracle documentation.


It’s possible to tune your Oracle export and Import jobs with the above two parameters. Just what values to use depends on many factors, such as your operating system, how much memory you have available and which Oracle database you are using. So read more about it in the Oracle export and import documentation.

Oftentimes, when creating indexes – and this is especially true when doing it in parallel – you can run into TEMP space problems. Check out my article on TEMP space monitoring to learn how to avoid that problem.

Like it, share it...

Category: Export/Import

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *