Quote:
Originally posted by DcsoBob
Nope, my hardware is not an issue. Plenty of ram, cpu, etc to do the job. Import is not my real area of concern.
My goal is to minimize the export time of the database. I don't want to be sitting around for 90 mins waiting for the export if I could have got it done in 45 by manipulating the export. Our vendor, who designed both the app and the database, has told me that he plans to drop a dozen of the largest indexes in the system before exporting. He claims this will reduce the export time of the database by a fair amount. I am attempting to prove/disprove this area.
As you've already experienced, and people have already responed, dropping indexes before an export will not speed up the export process significantly. When Oracle exports your database, it exports the data along with the table, but not with the index. The only thing the .dmp file knows is how to rebuild the index. (Which, by the way, it sometimes does wrong.)
Your consultant may be suggesting to drop the indexes before the export to help speed up the import. Maybe he is planning on rebuilding indexes in parallel once the data is already in the database.
Quote:
Now, I understand that within the export file the DDL is seperate from the data. This allows you the option of Rows=N so that you can just rebuild the objects themselves. But, with regards to the index itself, what I gather from your inputs is that even though that index is occupying physical space within my database, it is not exported. Only the index definintion (DDL) gets exported. Thus, on import, the index is created.
By george, I think he's got it.
Quote:
Have I got it now? If all this is true, then there appears to be little that I can do to speed up the full export other than having all users log off so that Oracle has the machines full CPU and other resources to do the export.
Have you played around with:
1. BUFFER size (which there is a bug in big buffers)
2. DIRECT=Y
3. Placing your .dmp file on stripped disk
4. Parallel exports by schema
Personally, I think export/import for migration purposes is a waste of time. I think you should clone your database from the old box to the new box & use ODMA to migrate it. It will be 50 times faster and you won't have to worry about silly options like COMPRESS=N, CONSISTENT=Y, FOO=bar, etc.
And you've stopped using export/import for backup, right?