Day: May 5, 2020

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