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
This question is related to
sql
database
oracle
oracle11g
oracle-sqldeveloper
The answer is
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:
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).
- 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).
Source: Stackoverflow.com