Originally posted by Mr.Hanky
When you use SQL* COPY you must be connected to one of the databases involved, it does not matter which one but you MUST be connected to the source or target.

Check you db_links too.

MH
not true, you can connect from any database to perform COPY, however if you dont wanna generate twice network traffic you should do it from one of the two database you are moving the data

For example I have 3 instances running, dev817, rep817 and dev920

Code:
I am connected to dev920 and goingto move data between dev817 and rep817 (tried from 9.2.0 to 8.1.7 but got ORA-00904: invalid column name, guess bug?)

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
dev920

set copycommit 2
set arraysize 1000

SQL> copy from lsc/lsc@dev817 to lsc/lsc@rep817 create dept using select * from dept

Array fetch/bind size is 1000. (arraysize is 1000)
Will commit after every 2 array binds. (copycommit is 2)
Maximum long size is 80. (long is 80)
SQLRCN in cpytbl failed: -1075
Table DEPT created.

   5 rows selected from lsc@dev817.
   5 rows inserted into DEPT.
   5 rows committed into DEPT at lsc@rep817.
the data first travel from dev817 to dev920, i.e my session then from my session I send the data to rep817