Copy from One Database to Another Using Oracle SQL Developer - Connection Failed

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

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

How to copy a table from one database to another in Oracle

The COPY command will be obsoleted in future releases of SQL*Plus. SQL*Plus User's Guide and Reference-Release 9.2

There are several methods like using datapump or CTAS. You can also use Database Link.

SQL> CREATE DATABASE LINK dblink_QA_1
CONNECT TO QA_1_app_prop_SCHEMA
IDENTIFIED BY password
USING '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = uat-blah.foo.foo.com)(PORT = 2501)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = uat)))'; 

And change your insert statement to-

SQL> INSERT INTO QA_2.app_prop (prop_id, prop_name, prop_value)
SELECT prop_id, prop_name, prop_value FROM QA_1.app_prop@dblink_QA_1 WHERE prop_id IN 
(SELECT prop_id FROM app_env@dblink_QA_1 WHERE function = 'CCC');

Oracle Database Copy Failed Using SQL Developer

The problem was caused by datafile has reached its max size though. I have resolved the problem by following the answer of this discussion ORA-01652: unable to extend temp segment by 128 in tablespace SYSTEM: How to extend?

Anyway, thank you everyone for the help.

how can i copy table and data from one database to another using oracle

You can do that using datapump.

or if you want to copy from schema to schema

create table shecma2.old_table 
as 
select * from schema1.old_table

or using oracle sql developer - here is link.

Migrate SQL Server Database to Oracle using SQL Developer on same system not work

Most common reason I've seen the Migration Repository from being created correctly no newer instances of Oracle is that the user associated with the schema lacks QUOTA on the user's default tablespace.

Try something like this :

ALTER USER X QUOTA UNLIMITED ON Y

Where X is the USER and Y is the TABLESPACE

---

Related Topics