DBLINK to copy SQL data

Be aware of what happens around you

Database link is an efficient way to copy data between two databases.It reduces time to copy small data faster. The link works using the tnsnames service of the source database.

Copy down the sql statement from prod to dev

SQL>select * from prod.tab1 where emp_id in ('101','102');

we check the service name of source database

SQL>show parameter service;

service_name PROD

Login to dev and create a database link

SQL>create database link prod connect to test identified by password using 'PROD';

you can check using below query

SQL>select * from dba_database_links;

check the db link connectivity using dual with the dblink name

SQL>select * from dual@prod;

use CTAS to copy the table from prod to dev

SQL>create table dev.tab1 as (select * from prod.tab1 where emp_id in ('101','102'));

check the rows in dev.tab1 table

SQL> select * from dev.tab1

Published by kishan

You will know about me.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: