copy a table

[pvnad1]

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.

[davey23uk]

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

[pvnad1]

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.

[davey23uk]

how was it done before

[pvnad1]

I asked. IMP EXP was not used. That's the only info. I have

[pvnad1]

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?

[slimdave]

What are you supposed to do with records that are the same, or have the same PK, in both systems?

[pvnad1]

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.

[slimdave]

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?

[JMac]

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.