使用 oracle sql developer 从一个数据库复制到另一个数据库 - 连接失败

2021-12-30 00:00:00 sql database oracle11g oracle oracle-sqldeveloper

我正在尝试使用 oracle sql developer 将表从一个数据库复制到另一个数据库.我有用户名、密码和 SID.

I am trying to copy a table from one database to another using oracle sql developer. I have the username, password and SIDs.

copy from uname1/password1@SID1 to uname2/pwd2@SID2 insert table1 (*) using (select * from message_table);

但是我收到连接失败错误.

这两个数据库存在于不同的主机中(连接属性中的主机名不同).

The two databases are present in different host hosts (the hostname is different in connection properties).

表有500万条记录，导出/导入太麻烦

The table has 5 million records and is too cumbersome to export/import

推荐答案

copy 命令是 SQL*Plus 命令(不是 SQL Developer 命令).如果您为 SID1 和 SID2 设置了 tnsname 条目(例如尝试 tnsping)，您应该能够执行您的命令.

The copy command is a SQL*Plus command (not a SQL Developer command). If you have your tnsname entries setup for SID1 and SID2 (e.g. try a tnsping), you should be able to execute your command.

另一个假设是 table1 与 message_table 具有相同的列(并且这些列只有以下数据类型:CHAR、DATE、LONG、NUMBER 或 VARCHAR2).此外，使用插入命令，您需要关注主键(例如，您没有插入重复记录).

Another assumption is that table1 has the same columns as the message_table (and the columns have only the following data types: CHAR, DATE, LONG, NUMBER or VARCHAR2). Also, with an insert command, you would need to be concerned about primary keys (e.g. that you are not inserting duplicate records).

我在 SQL*Plus 中尝试了如下您的命令的变体(没有错误):

I tried a variation of your command as follows in SQL*Plus (with no errors):

copy from scott/tiger@db1 to scott/tiger@db2 create new_emp using select * from emp;

在我执行完上面的语句之后，我还截断了new_emp表，并执行了这个命令:

After I executed the above statement, I also truncate the new_emp table and executed this command:

copy from scott/tiger@db1 to scott/tiger@db2 insert new_emp using select * from emp;

使用 SQL Developer，您可以执行以下操作来执行复制对象的类似方法:

With SQL Developer, you could do the following to perform a similar approach to copying objects:

  1. 在工具栏上，选择工具>数据库复制.

  1. On the tool bar, select Tools>Database copy.

使用您想要的复制选项确定源连接和目标连接.

Identify source and destination connections with the copy options you would like.

对于对象类型，选择表格.

For object type, select table(s).

复制命令方法已经过时，其功能不会随着新数据类型的发布而更新.有许多更当前的方法，例如 Oracle 的数据泵(甚至对于表).

The copy command approach is old and its features are not being updated with the release of new data types. There are a number of more current approaches to this like Oracle's data pump (even for tables).

相关文章