40

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

Improve this question
8
  • Has the dbLink been set up? Will "select * from table@SID2" yield results? Commented Jun 27, 2014 at 19:07
  • connection description for remote database not found This is what I am getting when I tried sid2 from sid1 worksheet Commented Jun 27, 2014 at 19:10
  • Insufficient privileges to create a database link Commented Jun 27, 2014 at 19:23
  • Sounds like you need to have your DBA set up a dbLink. What makes it too cumbersome to export / import? Just the size? Commented Jun 27, 2014 at 19:31
  • yes, and the columns. There are around 100 columns. Commented Jun 27, 2014 at 19:49

1 Answer 1

Reset to default
74

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. enter image description here

  3. For object type, select table(s). enter image description here

  4. Specify the specific table(s) (e.g. table1). enter image description here

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

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Thanks Patrick. I am unable to find the object type under source and destination connections. I just have create object, Truncate object and drop object options.
I think I am using an older version. I will try this out and notify you
I know this feature is on SQL Developer 4.
Thanks a lot for the screenshots. So if I want to copy the data to a different table (say source table name is emp, but dest table name is emp1) is it possible I couldn't find this option anywhere
I am trying to copy only one table but still it checks for all the tables which delays copying of data. As in following snapshot, There are too many sql statements checking for all objects before the final "select * from source" statement. Any clue what is happening here? [![enter image description here][1]][1] [1]: i.sstatic.net/E583e.png
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.