如何从Oracle中的另一个数据库中创建一个表作为select？

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

是否可以使用来自不同数据库的SELECT创建表(在我的dev db中)?

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?

是否有语法来执行此操作,还是需要先创建数据库链接?

4 个解决方案

#1

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 :

除非创建了DB链接,否则Oracle无法查询其他数据库。如果存在数据库链接,正如您所说,您必须执行以下操作:

create tmp_table as select * from prod_schema.table@prod_db

#2

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

@Steve是正确的,必须有一个DB链接,但语法是:

create tmp_table as select * from table@dblink

#3

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;

#4

CREATE TABLE table_name
AS SELECT * FROM schema_name.table_name;