Re: Copying Tables between Databases using SQL ???

There are two method to do that..

first:

insert into <tablename>
select * from <tablename>@remotedbname where <condition>;

you need a rollback segment which is big enough to accommodate your data for this method.

second:

use SQL*Plus copy command:

set arraysize 1
set copycommit 1000
copy from <username>/<password>@remotedbname - using -
create [insert/append/truncate] <newtablename> - select * from <tablename>@remotedbname where <condition>

I hope that the above command is right.. I may miss a few words.. so please do check the menu before you go ahead and copy.

USing the second command, you are forcing Oracle to commit every thousand record. So you don't need to have a large rollback segment for the copy.

Winnie

GivenRandy wrote in message
<1998091312092600.IAA15476_at_ladder01.news.aol.com>...
>How do I copy tables between databases using SQL?
>For example, I have two database files, one with about
>20 tables, the other with about 10 tables. I want to
>copy selected parts of a resultset (using WHERE) from
>the larger database to the smaller one. But, this is
>going across database files. I don't know how to do
>that in SQL. Can someone help? Thanks.
>
>Randy Given
>GivenRandy_at_aol.com
>http://members.aol.com/GivenRandy
>public key at http://members.aol.com/GivenRandy/pgpkey.asc
Received on Sun Sep 13 1998 - 14:43:04 CDT