Transfering data from one database table to another database table
Hi Guys,
I have to two databases and have to transfer around 6 Lacs of record from one of the database tables to another database table.(schema for the two tables are same).Right now my ODBC application is doing this using cursors,but some memory usage problem is there(CPU usage goes to 100%).
So I would like to know if any other ways by which i can execute a query to transfer this bulk record.
Can I use 'select * from X to Y where condn=xx' with the help of database link(Y)? If so is any size limitation for this select transfer ?
And BTW Abhay on what basis have you said that using database link is not a good practise. What process gets slowed down? Either way you need to make a database connection. So whats wrong with database links.
Amar "There is a difference between knowing the path and walking the path."
My requirement is to transfer data from one table to another table of different database. So I think sql copy command only for copying tables between databases and this won't help.
Can I use like 'Insert into T2 as select * from T1 where '
where T1 is a db_link name for the second database and T1 is a table in the first database
{ () } USING : database string, e.g., scott/tiger@d:chicago-mktg
: ONE of the keywords: APPEND, CREATE, INSERT or REPLACE
: name of the destination table
: a comma-separated list of destination column aliases
: any valid SQL SELECT statement
A missing FROM or TO clause uses the current SQL*Plus connection.
sys@ACME.WORLD>
Im not able to show the exact syntax as the thread is chooping off few parts...
Last edited by adewri; 07-15-2003 at 12:55 PM.
Amar "There is a difference between knowing the path and walking the path."
Originally posted by adewri And BTW Abhay on what basis have you said that using database link is not a good practise. What process gets slowed down? Either way you need to make a database connection. So whats wrong with database links.
Frist let me list defects in using DB link.
* Password of Remote DB can be known to any user who has got sufficient privilages to see sys objects. Even when DB link is private to the user x, user y can know the login credentials of the remote DB.
* Causes memory/stack overflow at times & this may lead to abnormal shutdown of the DB.
* Many bugs reported while using DB Link.
Now how would DB Link slow up process
* NOT IN clauses wont just turn up over DBs if Tables in Q are big.
* Even EQUI joins will eat time, due many round trips via N/W.
Rather, you can do some think like this ( On Local Sever )
* Create Temp_Table.
* Load data from remote DB through COPY ( by setting arraysize conisderably high ).
* Pick the data from temp_table load it to main table using Insert..Select ( the desired rows ).
Abhay.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
Originally posted by abhaysk Frist let me list defects in using DB link.
* Password of Remote DB can be known to any user who has got sufficient privilages to see sys objects. Even when DB link is private to the user x, user y can know the login credentials of the remote DB.
Any way you need the password for connecting to remote database.
Originally posted by abhaysk
* Causes memory/stack overflow at times & this may lead to abnormal shutdown of the DB.
When ??? never faced such problem. Have been replicating databases , moving data to and fro via PL/SQL for last three years over db links, never faced such problem.
Originally posted by abhaysk
* Many bugs reported while using DB Link.
Name a few. Database link is nothing but simply a connect username/password@remotedb.
Originally posted by abhaysk
Now how would DB Link slow up process
* NOT IN clauses wont just turn up over DBs if Tables in Q are big.
* Even EQUI joins will eat time, due many round trips via N/W.
Rather, you can do some think like this ( On Local Sever )
* Create Temp_Table.
* Load data from remote DB through COPY ( by setting arraysize conisderably high ).
* Pick the data from temp_table load it to main table using Insert..Select ( the desired rows ).
Abhay.
What ever you said will be true for a normal connection as well.
Amar "There is a difference between knowing the path and walking the path."
Reply With Quote
Bookmarks