Importing 150,000 records - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 22

Thread: Importing 150,000 records

  1. #11
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by hany
    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.
    The next country I will visit is definetely Egypt!
    Use KGB's adice too :-)



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

  2. #12
    Join Date
    Apr 2002
    Posts
    14
    I have found that first dropping, then building indexes manually after an import helps if you have a bunch of indexes on that table.
    Will McCormick (OCP)
    http://www.ramius.net
    wmccormick@ramius.net

  3. #13
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    I noticed if you put buffer and commit you slow dwn import, I rather have a big rbs to speed up and bypass these two parameters...

  4. #14
    Join Date
    Jul 2000
    Location
    Amsterdam
    Posts
    234

    Import monitoring

    Hi,

    If you want to monitor what your import is doing.

    You can query the dba_segments view to see if the table is still growing. (select bytes/1024/102 from dba_segments where segment_name = 'imp_table'

    You can query the dba_indexes view to see if all your indexes are there. (select index_name from dba_indexes where table_name = 'imp_table'

    Or you can see if all your constraints are enforced. (select constraint_name from dba_constraints where table_name = 'imp_table';

    Just did an import of a 87 million rows table with 8 indexes and 13 constraints it took 22 hours. Seven hours to get the table data back (7.5 GB) in 13 hours to build the indexes ( 12 GB) 2 hours to enforce the constraints.

    HTH

    Tycho

  5. #15
    Join Date
    Dec 2001
    Posts
    221
    ya hany did you solved ur problem. in ur case it should ideally take 10-15 minutes.

    as mentioned in earlier posts, try to import only data skipping constraints and indexes. see the performance. still the same then see the rollback segments and log buffer size. see enought memory is allocated. actually many issues have to consdired to get a good performance. at OS level , at Database level.
    Santosh Jadhav
    8i OCP DBA

  6. #16
    Join Date
    Dec 2001
    Posts
    320

    i still need help

    hi all,
    i still need help, i'm trying to import again with the parameters Julian provided me :
    "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", but i'm afraid it's been importing for 30 minutes now, i queried Dba_segments and the table that i need to import doesn't seem to be growing.
    Please help.

  7. #17
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938

    Re: i still need help

    Originally posted by hany
    Please help.
    Did you disable the triggers and the table constraints before the import?

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

  8. #18
    Join Date
    Dec 2001
    Posts
    320
    The table didn't exist Julian, i'm creating a new user and importing the whole schema which contains that table.

  9. #19
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by hany
    The table didn't exist Julian, i'm creating a new user and importing the whole schema which contains that table.
    Could it be that there are actually much more than 150000 rows? Could it be that those rows are "big" as Jurij suggested. Could it be that your Network connection is slow? You mentioned that you are doing the import from home.

    Try Pando's suggestion: forget commit and buffer and use one big undo segment. Do you know how to do that?



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

  10. #20
    Join Date
    Dec 2001
    Posts
    320
    yes i do, i will try that too, thanks Julian, i'll inform you if it works (or if it doesn't)

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