copy a table
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: copy a table

  1. #1
    Join Date
    Mar 2004
    Posts
    53

    copy a table

    Hi all,

    I have a question that's bugging me for a while and I was wondering if somebody could help me. I have to perform a table copy from Table A on Oracle 8i at one location to Table B on Oracle 8i at some other location. Both the tables exist and are currently used. Now, I was asked to find the right way to copy. Earlier, they tried and it took 2 days to transfer 1/2 million records. Both the tables have same columns but for one column which appears out of order in Table B. I am thinking of using COPY command in SQL*PLUS.

    Now, my question is: How should this be performed? When I see the row count on both the tables, it changes every 2 secs. i.e thy are being used.

    Please give me some steps. Will appreciate any help. Thanks.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    2 days for half a million is pitiful, how did they do it.

    Export import would be faster.

    You could drop the second table and do a ctas or insert append over a db link

  3. #3
    Join Date
    Mar 2004
    Posts
    53
    Hi,

    Thanks very much for the fast reply. If you can also comment on my other questions it would be great. Normally in a prod. environment, what's the procedure? Both the tables are in use. I donot know if it is dba related only. However, I am supposed to propose a solution.

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    how was it done before

  5. #5
    Join Date
    Mar 2004
    Posts
    53
    I asked. IMP EXP was not used. That's the only info. I have

  6. #6
    Join Date
    Mar 2004
    Posts
    53
    I am not able to come to a definite conclusion and I want to make sure it works perfectly. Because there are 3 versions

    1. sql*loader
    2. sql*plus copy
    3. imp exp.

    In my case I have two tables that have same columns but with a slight change in col order. Eg: Table A has A B C D E, table B has A B D E C

    Any suggestions please?

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    What are you supposed to do with records that are the same, or have the same PK, in both systems?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    Mar 2004
    Posts
    53
    Hi,

    The req. is that I need o copy all records with creation date less than current date from A to B. This is a one time backup ONLY. Both the tables have same columns. Only one field is out of order. Both have PKS. One in each and they are same.

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by pvnad1
    One in each and they are same.
    So whatdo you do if you have a new record to insert to the target table, and it has the same PK value as a record already in the table?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  10. #10
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    In my case I have two tables that have same columns but with a slight change in col order. Eg: Table A has A B C D E, table B has A B D E C
    Imp and Exp and cater for that without problem.

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