copy from one database to another using oracle sql developer - connection failed

Question

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);

However I am getting the connection failed error.

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

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

Answer 1

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.

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).

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;

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;

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

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

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

3. For object type, select table(s).

4. Specify the specific table(s) (e.g. table1).

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).