This discussion is archived
6 Replies Latest reply: Oct 9, 2012 11:53 AM by orausern RSS

Import handing since several hours

orausern Explorer
Currently Being Moderated
Hi,
I am facing an issue while copying a schema using import. The source schema is on Oracle 11.2.0.2 and the target is on 11.2.0.1. So I took export using 11.2.0.2 and also doing importing using 11.2.0.2 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

like:
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?

Thanks,

Edited by: orausern on Oct 9, 2012 5:49 AM
  • 1. Re: Import handing since several hours
    964511 Newbie
    Currently Being Moderated
    Hi..
    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
    Fran Guru
    Currently Being Moderated
    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_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 11.2.0.1, you must use 11.2.0.3, less bugs..
  • 3. Re: Import handing since several hours
    orausern Explorer
    Currently Being Moderated
    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 11.2.0.2 and target is at 11.2.0.1. 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?

    Thanks,
  • 4. Re: Import handing since several hours
    Fran Guru
    Currently Being Moderated
    You can use data pump in different databases and different versions:
    http://docs.oracle.com/cd/B19306_01/server.102/b14215/dp_overview.htm#CEGFCFFI

    just remember to user VERSION parameter when you run the expdp:
    http://docs.oracle.com/cd/B19306_01/server.102/b14215/dp_export.htm

    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
    N K Pro
    Currently Being Moderated
    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
    orausern Explorer
    Currently Being Moderated
    Awesome help!!! My issue got resolved once I tried with expdp. Thanks a LOT experts!!!!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points