-
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)
-
create a db link and use the
create table as select from ..... command
Ronnie
ronnie_yours@yahoo.com
You can if you think you can.
-
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
-
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?!!
-
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
Ronnie
ronnie_yours@yahoo.com
You can if you think you can.
-
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
-
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.
Ronnie
ronnie_yours@yahoo.com
You can if you think you can.
-
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
-
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.
Ronnie
ronnie_yours@yahoo.com
You can if you think you can.
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|