Importing 150,000 records
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 22

Thread: Importing 150,000 records

  1. #1
    Join Date
    Dec 2001
    Posts
    320
    Hi,
    why does importing 150,000 records take so much time?, i tried to import a table containing 150,000 records and i took like 6 hours, is there any way or method to make such a thing in a faster way ??
    Thanks for your help.

  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    6h is strange. It should not even take an hour, not even half an hour.

    Could you please show me the IMP statement you run, namely I am interested in BUFFER and the output from select * from v$rollstat;

    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Well 150.000 records doesn't say much about the work to be done to import that table. It could be tat he space reqired for those 150.000 records would be something like 200 GB, or it could be only 200KB.

    Not to say that we don't know anything about your system configuration and your hardware horse power.

    So 6 hours might be redicolously long time, or it could be an excelent time score...
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Dec 2001
    Posts
    320
    well i'm not using a batch file, i'm just typing imp on the command prompt and i leave all the defaults as is, providing only the username and password, i'm home now so i can't query the v$rollstat view.
    The Database is Oracle 8.1.7

  5. #5
    Join Date
    Apr 2002
    Posts
    2
    Setting the buffer and commit=n parameter could improve performance.

  6. #6
    Join Date
    Dec 2001
    Posts
    320
    i'm talking about a Hp 3000 Server, with 20GB of Hard Disk, but i'm not sure about the size of the datafile, as for the operating system it is Windows 2000 Server.

  7. #7
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    As Jurij said, the data might be a lot: there are row and rows. Has it taken before that long?

    Try to run:
    Code:
    imp userid=user_name/password buffer=30000 file=name.dmp tables=table_name grants=N ignore=Y commit=Y fromuser=user_name touser=user_name
    Do you disable first the table constraints and the triggers?
    30000 might be too much or too little, I don't know, I would have prefered to see your undo segments...



    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  8. #8
    Join Date
    Dec 2001
    Posts
    320
    Thank your for your help Julian, i will sure try that one, by the way what happened to your visit to Egypt? i thought you wanted to come, well be sure that you're always welcome.

  9. #9
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    684
    If number of database buffers is low and shared pool size is small, importing may take forever !!!

    Try to ecrease that parameters and you will see the result.
    Best wishes!
    Dmitri

  10. #10
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    684
    If your table do not exists before import operation, table constraints are created after data importing.
    Best wishes!
    Dmitri

Posting Permissions

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


Click Here to Expand Forum to Full Width