Copy a very big table from one database to other
Hi
I have to copy a very big table from one database o another database. This table has around 74080422 rows and the AVG_ROW_LEN is 151.
I have tried with export/import but it took 1 hour in the export and the import was running 3 days and it didn't finish.
I have tried to make a create table as select * from cargos@db1.
I mean using a database link but we waited 12 hours and it didn't finish, the temp tablespace was full, this terminate the process. The temp tablespace has 14 gigas.
Does anyone know a different way to copy big tables??
Does anyone have a suggestion?
Regards
Anabel
Re: Copy a very big table from one database to other
Quote:
Originally posted by Anabel Paredes
I have tried to make a create table as select * from cargos@db1.
I mean using a database link but we waited 12 hours and it didn't finish, the temp tablespace was full, this terminate the process. The temp tablespace has 14 gigas.
Something can't be right with the above description. "create table as select * from cargos@db1" doesn't need to perform any sorting whatsoever, so it doesn't need any space in temp tablespace. You couldn't run out of temp tablespace, it must have been something else. Perhaps run out of space in your rollback tablespace?
How about using SQL*Plus's COPY command? It has been made available just for the tasks like yours. Its surprisingly fast and you can specify the frequency of commits to avoid RBS problems....