Using Database Link in Oracle

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;

Leave a Reply

Your email address will not be published. Required fields are marked *

*

* Copy This Password *

* Type Or Paste Password Here *

41,728 Spam Comments Blocked so far by Spam Free Wordpress

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>