CODEWITHSUNDEEP
sqloracle-database
chris
chris

Reputation: 37480

How can I create a table as a select from another database in Oracle?

Is it possible to create a table (in my dev db) using a SELECT from a different database?

I want something like:

create tmp_table as select * from prod_db.prod_schema.table

Is there syntax to do this, or do I need to create a database link first?

Upvotes: 10

Views: 53239

Answers (5)

AxeSkull
AxeSkull

Reputation: 33

If anyone is getting ORA-00901: invalid CREATE command with @chris's answer, try adding TABLE after CREATE:

CREATE TABLE TEMP_TABLE AS SELECT * FROM TABLE@DB_LINK;

Upvotes: 0

Ameya Save
Ameya Save

Reputation: 11

CREATE TABLE table_name
AS SELECT * FROM schema_name.table_name;

Upvotes: -2

Mark Harrison
Mark Harrison

Reputation: 304662

Don't forget to create your indexes. You can get this for all the tables in your schema with a query like this:

SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name)
     FROM USER_INDEXES u;

Upvotes: 1

Steve Schnepp
Steve Schnepp

Reputation: 4710

You have to create a datalink first.

Oracle cannot query other databases unless a DB link is created. If a DB link exists, as you remarked, you have to do :

create tmp_table as select * from prod_schema.table@prod_db

Upvotes: 12

chris
chris

Reputation: 37480

@Steve is correct that there has to be a DB Link, but the syntax is:

create tmp_table as select * from table@dblink

Upvotes: 4

Related Questions