Oracle: Improve Import/ Export performance (imp / exp)

EXPORT:
    * Set the BUFFER parameter to a high value (e.g. 2M)
    * Set the RECORDLENGTH parameter to a high value (e.g. 64K)
    * Stop unnecessary applications to free-up resources for your job.
    * If you run multiple export sessions, ensure they write to different physical disks.
    * DO NOT export to an NFS mounted filesystem. It will take forever.

IMPORT:
    * Create an indexfile so that you can create indexes AFTER you have imported data. Do this by setting INDEXFILE to a filename and then import. No data will be imported but a file containing index definitions will be created. You must edit this file afterwards and supply the passwords for the schemas on all CONNECT statements.
    * Place the file to be imported on a separate physical disk from the oracle data files
    * Increase DB_CACHE_SIZE (DB_BLOCK_BUFFERS prior to 9i) considerably in the init$SID.ora file
    * Set the LOG_BUFFER to a big value and restart oracle.
    * Stop redo log archiving if it is running (ALTER DATABASE NOARCHIVELOG;)
    * Create a BIG tablespace with a BIG rollback segment inside. Set all other rollback segments offline (except the SYSTEM rollback segment of course). The rollback segment must be as big as your biggest table (I think?)
    * Use COMMIT=N in the import parameter file if you can afford it
    * Use ANALYZE=N in the import parameter file to avoid time consuming ANALYZE statements
    * Remember to run the indexfile previously created


Examples:
     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

Monitoring import performance

select
   substr(sql_text,instr(sql_text,'into "'),30) table_name,
   rows_processed, round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes,
   trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_minute
from  
   sys.v_$sqlarea
where 
   sql_text like 'insert %into "%' and command_type = 2 and open_versions > 0;

Related Posts:
- Query optimization tips for Oracle
- How to Get Execution Plan and Statistics of Query
- DBMS_PROFILER: How to analyze pl/sql performance
- Oracle : TKPROF simple steps
- Why do I prefer COALESCE over NVL
- Append String to CLOB in Optimized way