Re: CREATE TABLE AS

You will need to create a database link to the 'other' database.

I would suggest connecting to the database where newtable exists, and create a database link to the oldtable database.

CREATE DATABASE LINK OLDDATABASE
  CONNECT TO SCOTT
  IDENTIFIED BY TIGER
  USING 'OLDDATABASE'; where OLDDATABASE is the SQL*Net alias in the TNSNAMES.ORA file.

Then the statement would read;

CREATE TABLE NEWTABLE
  AS SELECT * FROM OLDTABLE_at_OLDDATABASE; n.b. If you are moving a large table, you may want to think about Rollback Segments.

Hope this helps,
Andrew

soukch_at_my-dejanews.com wrote:

> I want to copy Oracle table from one database to another database, Oracle
> suggested using CREATE TABLE AS and INSERT but there's no example. I know how
> to use it when the from and to are in the same database:
>
> create table newtable
> as select * from oldtable
>
> How would I specify the from and to when it deal with 2 database.
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Wed Apr 07 1999 - 22:46:37 CDT