(Updates at the bottom)
We've all faced that fun situation where we want to reorg large amounts of data in small amounts of time. Here are some guidelines that should get your reorgs flying along
You may consider taking advantage of the _disable_logging = true parameter in init.ora.
With _disable_logging = true, the call to write redo is still made, but the actual file-write it not made. Thus you want to make the redo files extremely large as checkpoints still occur when the redo logs are nominally full.
Of course, there is no such thing as free lunch. Without logging if you have any kind of server crash, then there is no way back. Thus this is a good parameter to have for populating (say) a test instance. For a production instance, you want to be very sure of your backups before using this.
Thanks for Steve Perry for this additional information
I think the recordlength defaults to 1k if you don't include it along with the direct=y. It can be as large as 64k. On 8.1.7/NT, I tested it a bit and and took an export from 1hr 45mins to 55 mins. The export does appear to be a little bit bigger.
And the following from Metalink Note 155477.1
2. You may be able to improve performance by increasing the value of the RECORDLENGTH parameter when you invoke a direct path Export. Your exact performance gain depends upon the following factors: - DB_BLOCK_SIZE - the types of columns in your table - your I/O layout (the drive receiving the export file should be separate from the disk drive where the database files reside) The following values are generally recommended for RECORDLENGTH: - multiples of the file system I/O block size - multiples of DB_BLOCK_SIZE For example, invoking a Direct path Export with a maximum I/O buffer of 64kb can improve the performance of the Export with almost 50%. This can be achieved by specifying the additional Export parameters DIRECT and RECORDLENGTH. E.g.: exp userid=system/manager full=y direct=y recordlength=65535 file=exp_full.dmp log=exp_full.log imp userid=system/manager full=y recordlength=65535 file=exp_full.dmp log=imp_full.log
Thanks for Ming Mendonca for this additional information
Be aware of direct path export with unused columns
The error is related to Bug.2829681 "DIRECT IMPORT YIELDS IMP-20"
Direct path export for the table having unused column is not supported.
From Oracle 9.2 onwards oracle automatically convert direct path export to conventional path for table having unused columns.