Suppose data from a table X in database D is to be copied into table Y in the same database, then the problem can be solved easily by executing the query –
Assumption –
X(C(1),C(2),C(3)….C(N)) == Y(C(1),C(2),C(3)….C(N)) where C(1),C(2),etc. denote the columns of the table.
INSERT INTO Y SELECT * FROM X;
COMMIT;
Problem –
Data from a table X in database D1 has to be copied to table Y in database D2.
Assumption –
X(C(1),C(2),C(3)….C(N)) == Y(C(1),C(2),C(3)….C(N)) where C(1),C(2),etc. denote the columns of the table.
Workaround –
Oracle SQL Developer allows you to create INSERT query for each record in the table. The queries generated can be executed in database D2. But, suppose the table contains 2,00,000 records, it would take approximately 30 minutes to generate the INSERT script and almost an hour to execute the script.
Solution –
Login as SYSDBA(Database Administrator) using the query –
CONNECT SYS/ AS SYSDBA;
Allow the user to create database links using the query –
GRANT CREATE DATABASE LINK TO <userSchemaName(D2 in this case)>;
Connect to schema D2 and execute the query –
CREATE DATABASE LINK DATABASE_LINK_NAME CONNECT TO “SCHEMA_NAME(D1 IN THIS CASE)” IDENTIFIED BY “”
USING ‘(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = HOST_NAME)(PORT = XXXX))(CONNECT_DATA = (SERVICE_NAME = ##SERVICE_NAME##)))’;
Execute the query –
INSERT INTO Y
SELCT * FROM X@DATABASE_LINK_NAME;
COMMIT;
