Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Location
    kualalumpur
    Posts
    89

    Unanswered: problem importing data..

    Hello Oracle DBAs ,
    Iam trying to do a import of a .dmp file.. sized morethan 5gb on windows.
    after importing for 1/2 hr. For a perticuler table.It keep processing(for 3 days..and still on..)..Doesnt show any errors...and consumes most of cpu time.
    I tried to monitor progress of import using following script..
    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;

    it shows no. of rows processed (over 9 million) in min.s(10k) but..when I run the same script after some time..it shows the same figures(9mill n..10k).

    if the .dmp file is having problem why its not rising the error..
    v have waited the import to process but even after 3 days it still processing..

    Can anyone please help ..

    Thanks.
    sridhar

  2. #2
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    This link answers lots of questions about Import/Export, and should help you. Sorry I can't answer your specific question...

    http://www.orafaq.org/faqiexp.htm
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    I suggest a few things:

    1. do an imp with indexfile and rows=y to see how many rows are in the table in question
    2. first import the tables with constraints=N and indexes=N but rows=Y
    this way you eliminate any overhead due to the import creating indexes and enabling constraints on those large tables.
    Once that import is done, import the constraints and indexes with rows=N and ignore=y
    3. I would also use the FEEDBACK option to see progress and set that to 500k or something so you can see how the load progress for the large tables is going.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •