使用SQL将Oracle数据库表的副本创建到另一个Oracle数据库(Create copy of Oracle database table to another Oracle database using SQL)

我想知道是否有一种简单的方法可以将整个Oracle数据库表或数据行复制到另一个Oracle数据库表(不同的数据库)中。 是否有某种方法可以使用SQL语言执行此操作? 我最初的想法是使用这样的SQL查询:

INSERT INTO outputTable SELECT * FROM username/password@instance.inputTable

如果没有SQL,还有另一种方法吗? 我正在尝试自动执行此任务,因此使用SQL Developer执行复制或其他方面的操作是不够的。 我试图用Python做到这一点。

I am wondering if there is a simple way to copy either an entire Oracle database table or data rows into another Oracle database table (different databases). Is there some type of way to do this using SQL language? My initial thought is to use a SQL query like this:

INSERT INTO outputTable SELECT * FROM username/password@instance.inputTable

If not with SQL, is there another way to do this? I am trying to automate this task so performing a copy using SQL Developer or something along those lines will not suffice unfortunately. I am trying to do this using Python.

最满意答案

您可以创建DBLink来连接源数据库和目标数据库,然后只需使用SQL将数据保存在链接的数据库中。

就像是:

create database link "TARGET" connect to YOUR_SCHEMA identified by YOUR_PASSWORD using YOUR_CONNECTION_STRING;

拥有DBLink后,无论所涉及的表位于不同的DB上,都可以使用纯SQL:

INSERT INTO YOUR_TABLE@TARGET SELECT * FROM YOUR_TABLE

You can create a DBLink to connect the source and the target DB, then simply use SQL to save your data in the linked DB.

Something like:

create database link "TARGET" connect to YOUR_SCHEMA identified by YOUR_PASSWORD using YOUR_CONNECTION_STRING;

Once you have a DBLink, you can use plain SQL, no matter the involved tables are on different DB:

INSERT INTO YOUR_TABLE@TARGET SELECT * FROM YOUR_TABLE

更多推荐