Screaming imports


(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

Planning.

Why do you pay big bucks for Oracle? Because if everything is well planned then you probably won't need to do reorgs...ever. Get your space usage and re-usage correct and reorgs are a thing of the past. Make the monster reorg you're about to do the last one you need to do.

Fast exports

Fast imports

Emergency options

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.


Update

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

Update

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.