Re: How to speed up the import fase of a DB?

On Wed, 27 May 1998 09:16:30 GMT, mainini_at_hotmail.com wrote:

>Hi,
>
>I need to make an export/import of a very large database (for
>reorganization purposes). I've tryed already the procedure (via
>exp/imp tools) a couple of times, but the import fase is taking too
>much time and I can't effort too much downtime.
>Does anyone know how to speed up the import fase?
>Thank you very much...
>
> Marco.

The following suggestions have not been researched, nor have I tried them but:

1. Increase the default temporary tablespace parameters and sort_area_size values to very high values to speed up index creation.
2. If you have sufficient number of cpus, and disks import the export file using mutltiple imports, specifying a list of tables. You could likewise to table exports to generate multiple export files, together with a full export specifying rows = N to ensure you get all non-table related objects.
3. Specify indexes=N and create the indexes manually specifying no_logging. (assuming version 8?). Also for primay keys.
4. Create the tables first with constraints, disable the constraints. Re-enable the constraints afterwards with the novalidate option. (Import may do this anyway, I've not checked).
5. Use a very large redo logs to avoid checkpoints at log switch.
6. Make sure the database is in NOARCHIVELOG mode when the import is run.