Copy a Remote Table

[monna]

Hi,
What would be the fastest/better way to copy a table from a remote DB?


Remote table has 8,000,000 records
O/S Windows NT
From Oracle8i to Oracle 7 (Release 7.3.4)

[ronnie]

create a db link and use the

create table as select from ..... command

[monna]

SQLPLUS copy is a good idea?
In this case I can break the transaction into smaller entries.
But I am not sure,the amount of data is big.

Thanks

[monna]

That was my first thought but if I use :
create my_table
unrecoverable
as
select * from table@remote;

I will have a big transaction ,the commit will take place after all the 8,000,000 have been inserted.
Or I might be wrong?!!

[ronnie]

Originally posted by monna
That was my first thought but if I use :
create my_table
unrecoverable
as
select * from table@remote;

I will have a big transaction ,the commit will take place after all the 8,000,000 have been inserted.
Or I might be wrong?!!

You dont have to worry about commit/rollback because its a DDL.

No rollback will be generated for this.

Hope this helps

[aarroyob]

Originally posted by ronnie
You dont have to worry about commit/rollback because its a DDL.

No rollback will be generated for this.

Hope this helps [/B]

Big temp is requiered for this operation. I think in your case is not the best option. Use copy, or just export the table in origin and import it in destiny with ignore=yes, constraints=no, indexes=no.

Cheers

Angel

[ronnie]

Originally posted by aarroyob

Originally posted by ronnie
You dont have to worry about commit/rollback because its a DDL.

No rollback will be generated for this.

Hope this helps

Big temp is requiered for this operation. I think in your case is not the best option. Use copy, or just export the table in origin and import it in destiny with ignore=yes, constraints=no, indexes=no.

Cheers

Angel [/B]

You are right about temp angel. I should have mentioned that.

And yes export/import can be another way to do it.

[monna]

Please do not take me wrong, I do not want to discover that the earth is round but is something that is missing.

You say that when:
create table as select * from table@remote has nothing to do with the rollback seg.
I think there is an “insert “ involved in this create table so there is a transaction.
How the data is “moved” into a new table?

thanks

[ronnie]

Originally posted by monna
Please do not take me wrong, I do not want to discover that the earth is round but is something that is missing.

You say that when:
create table as select * from table@remote has nothing to do with the rollback seg.
I think there is an “insert “ involved in this create table so there is a transaction.
How the data is “moved” into a new table?

thanks

I am not aware as to how this happens internally but you can do a small test

use the create table as select * from ... command and then issue a rollback . You will notice that the data is still there in the table which got created. Its not rolled back. This shows that there is no before image of the data.

[ramaral]

You're best bet is doing a sqlplus copy command with the commit parameter.

All three options, import/export, create table as select, and sqlplus copy, will be slow with such a big table. But with the copy command set with the commit parameter at least you will not waste all of the time in the copying of the data if something happens.