Oracle Import/ Export FAQ

$Date: 26-Jan-1999 $
$Revision: 1.2 $
$Author: Frank Naudé $

Topics

Unlike Oracle's SQL*Loader, the Export and Import utilities uses the SQL layer for data transfer and are therefor slower. The export utility can be effectively used, however, to detect database corruption and eliminate fragmentation. The DBA may want to perform exports of selected database tables to tape, for example, at longer intervals (multi-tape exports specified with tape volume size), especially if there are doubts regarding the reliability of the hardware. An export provides a minimal check of the hardware by ensuring that all the data can be read (some problems are not caught, however, unless the data is actually imported).

Method 1:

  select substr(sql_text,14,instr(sql_text,'(')-16) 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_min
  from   sys.v$sqlarea
  where  sql_text like 'INSERT INTO "%'
    and  command_type = 2
    and  open_versions > 0

If your import has more than one table, this statement will only show information about the current table being imported.

Contributed by Osvaldo Ancarola, Bs. As. Argentina.

Method 2:

Use the FEEDBACK=n parameter as part of the IMP command. This command will let IMP displays a dot for every n rows imported.

Create a compressed export on the fly. Depending on the type of data, you probably can export up to 10 gigabytes to a single file.

This example uses gzip. It offers the best compression I know of, but you can substitute compress, zip, or whatever.

        # create a named pipe
        mknod exp.pipe p
        # read the pipe - output to zip file in the background
        gzip < exp.pipe > scott.exp.gz &
        # feed the pipe
        exp userid=scott/tiger file=exp.pipe ...

Read the Oracle/Unix FAQ for more examples on using Unix pipes.

• Set the BUFFER parameter to a high value (eg. 2800000)
• Make sure all applications on the site has been taken down
• Make sure the export file is on a physical drive not used by oracle
• DO NOT export to a NFS mounted filesystem. It will take forever.

• 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_BLOCK_BUFFERS 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
• Remember to run the indexfile previously created

• ORA-01555: Snapshot too old - Ask you users to STOP working while you are exporting or use CONSISTENT=NO

• ORA-01562: Failed to extend rollback segment - Set COMMIT=Y while importing