chris chris - 2 years ago 75
SQL Question

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?

Answer Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download