Oracle Database Link

Were you asked to move data from production table to development or vice versa? If you are a database administrator, you might have at least one request to move data. There are so many ways to skin a cat so let me introduce the Oracle object called Database Link a.k.a db link.  A Database Link is an Oracle object that can access objects from a remote database instance as long as the user has the proper grants, privileges and user accounts.

Database links are good for small data move, you might consider using Export Import data pump for larger data sets. There are two types of db links available in Oracle. Public and Private.

Public db links are dangerous because anyone can use it This is not a recommended practice unless someone comes up with a strong reason.

Private db links are the way to go as they is very secure and only be accessible to the owner of schema. The another reason why it is secure is that you need to logged as owner to use it.

DB Link: Create Syntax Private

CREATE DATABASE LINK db_link_name CONNECT TO scott IDENTIFIED BY tiger USING 
'production_db'; 

Where do you create dblink? remote database or local database?  It should be created on local database and the purpose is to access remote database.

DB Link: Create Syntax Public

CREATE PUBLIC DATABASE LINK db_link_name 
CONNECT TO scott IDENTIFIED BY tiger USING 'production_db'; 

How do you check if the db link is working or not?
Run the below query on local database to see if you get any result back.

SELECT * 
FROM   dual@db_link_name; 

Note:  dba_db_links View stores the database link information.