DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: copying large table

  1. #1
    Join Date
    Nov 2001
    Posts
    110

    copying large table

    What is the fastest way to copy a large table(15 million) records from development into test env.
    Thanks.

  2. #2
    Join Date
    Dec 2001
    Location
    Brazil
    Posts
    282
    Have u tried sqlplus copy command? Or just exp/imp.

    F Melo

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    db link + insert append

  4. #4
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by pando
    db link + insert append
    Plus parallel if possible.
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  5. #5
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Or Copy in N sessions if you can logically break up ur Rows in the table .
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  6. #6
    Join Date
    Apr 2003
    Posts
    353
    Originally posted by pando
    db link + insert append
    Use ctas than insert..

    SQL> create table ctas nologging as select * from acc_grn_select@dbls;

    Table created.

    real: 1109
    SQL> select count(*) from ctas;

    COUNT(*)
    ---------
    74006

    real: 203
    SQL> truncate table ctas;

    Table truncated.

    real: 140
    SQL> insert into ctas /*+append*/ nologging select * from acc_grn_select@dbls;

    74006 rows created.

    real: 19828

    We used to copy 5 million records with ctas in less than 5 minutes.

  7. #7
    Join Date
    Aug 2002
    Posts
    176
    Wouldnt creating table with a non matching condition 1=2 be better ??
    Success Consists of Getting Up Just One More Time Than You've Fallen Down
    Be Blessed

  8. #8
    Join Date
    Apr 2003
    Posts
    353
    he needs the table WITH RECORDS

  9. #9
    Join Date
    Jan 2004
    Posts
    162
    > Use ctas than insert..

    Perhaps if you specified the /*+ APPEND */ hint and NOLOGGING option correctly you would find INSERT was comparable to CTAS?

    Padders

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