6 Replies Latest reply: Oct 9, 2012 1:53 PM by orausern RSS

    Import handing since several hours

      I am facing an issue while copying a schema using import. The source schema is on Oracle and the target is on So I took export using and also doing importing using version of import utility (to avoid version related issues). The schema dump file is just 150 mb. The target db server is in different country than the source db server and I added tns-entry of the target db and I am running import from source database using tns entry

      imp <system/pwd@targetdb> fromuser=db1 touser=db1 file=db1.dmp log=db1_imp.log
      However the import is hanging since more than 4 hours. There is a table with about 450k rows and the import is stuck on that table since then. Must I run impdp and kill this imp session or is there something else that can help?


      Edited by: orausern on Oct 9, 2012 5:49 AM
        • 1. Re: Import handing since several hours
          Please copy the dumpfile to target server and start import form target server, it will be much faster
          • 2. Re: Import handing since several hours
            select substr(sql_text,instr(sql_text,'INTO "'),30) table_name,
            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 you get 0 rows, the import is stop, you should stop it and try with data pump.

            Also, you are using version, you must use, less bugs..
            • 3. Re: Import handing since several hours
              Hi Fran,

              I got 0 rows as output of your query so I am going ahead with the data pump solution. Now the issue is the source and target db are at different versions -source is at and target is at So do I try to get it done using network_link, will that work? Are there some ways to tune the import session where the import is being done in different a server in a different country than the source server so a lots of network is involved?

              • 4. Re: Import handing since several hours
                You can use data pump in different databases and different versions:

                just remember to user VERSION parameter when you run the expdp:

                when the expdp finish just move the dumpfile to the server where the target database is and run the impdp.
                • 5. Re: Import handing since several hours
                  You should also check there are no triggers being fired while you do the import which might corrupt/generate innecesary data.
                  • 6. Re: Import handing since several hours
                    Awesome help!!! My issue got resolved once I tried with expdp. Thanks a LOT experts!!!!