2 Replies Latest reply: Dec 13, 2013 1:39 PM by jgarry RSS

    Speed up oracle imp

    rsar001

      Hi Folks,

      We are importing table data from oracle 9i (9.2.0.8) into oracle 11g (11.2.0.3) using the old oracle imp utility, and we're hoping that you can help us speed up the process please.

       

      We have exported one table data; roughly 35 million rows have been exported generating an export dump size of 7.5GB. Please note that this is a subset of the data (one month worth of data to be exact). We do realize that there will be a lot of duplicates within this data sub-set and that the import would reject the duplicated rows through PK constraints (giving the following error):

       

      IMP-00019: row rejected due to ORACLE error 1

      IMP-00003: ORACLE error 1 encountered

      ORA-00001: unique constraint (RG_SCHEMA.SSTG010P) violated

      Column 1 243113850256342640

       

      The import has been running for over 3 hrs now and is taking quite a while even though we're running it with the following parameters:

       

      statistics=NONE

      buffer=12000000

      resumable=Y

      ignore=Y

      constraints=N

      indexes=N

       

      We do realize rejecting more than half the rows would slow down the process as there is overhead associated with that; having said that is there anything else we can try to to speed up this dreaded process please.

       

      Appreciate the help.

        • 1. Re: Speed up oracle imp
          TSharma-Oracle

          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
          • 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 STATISTICS=NONE in the import parameter file to avoid time consuming to import the statistics
          • Remember to run the indexfile previously created

          Import Export FAQ - Oracle FAQ

          • 2. Re: Speed up oracle imp
            jgarry

            Besides all the stuff TSharma said, the big slowdown is probably the handling of the errors.

             

            Three ways to deal with it off the top of my head:

             

            Create a text file with sqlplus to load with sql*loader DIRECT load (if you can), removing the dups before writing text file (how to skip duplicates is a faq).

             

            Create the table in a different schema without the constraint, transfer over to real table with a statement that skips duplicates.  It may also help to do this on originating side, then exp/imp with DIRECT and recordlength=65535.

             

            Use the QUERY parameter on export to remove duplicates (I haven't thought through if that is possible).