Firstly, is it safe to assume that you have set up a database link between the two databases?
If not then see
CREATE DATABASE LINK[
^]
Your code snippet
COPY FROM olddbusername/olddbpassword
TO new/newpassword
contradicts your statement
Quote:
both have same values and tables
In any event note the following
Quote:
The COPY command will be deprecated in future releases of SQL*Plus. After Oracle 9i, no new datatypes are supported by COPY.
SQL*Plus COPY Command[
^]
Looking at your other code snippet
INSERT INTO contacts
(contact_id, last_name, first_name, address)
VALUES using select * from contacts
Firstly, you should never use
SELECT *
when inserting into a table - list the columns explicitly.
Secondly, you will need to indicate which of the tables in is this database and which in the "other" database. So assuming you set up the database link as
olddb
you would need something like
INSERT INTO contacts
(contact_id, last_name, first_name, address)
VALUES select contact_id, last_name, first_name, address from contacts@olddb
Caveat: Untested code