使用oracle sql developer从一个数据库复制到另一个数据库-连接失败 [英] copy from one database to another using oracle sql developer - connection failed
问题描述
我正在尝试使用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
推荐答案
副本命令是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:
-
在工具栏上，选择工具>数据库副本。
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).
这篇关于使用oracle sql developer从一个数据库复制到另一个数据库-连接失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
