Database links

 

What is a database link?

        Imagine you have two different databases—let's call them Database A and Database B. Now, what if you wanted to make Database A talk to Database B and share information between them? That's where a database link comes in.

A database link is like a bridge between Database A and Database B. It lets you access tables, views, and other objects in Database B from Database A. This doesn't mean that Database B has to be an Oracle database; it can be any type of database, but you'll need Oracle Heterogeneous Services to connect to non-Oracle systems.

Once you've set up this bridge (DB link), you can use it to write SQL statements in Database A that refer to objects in Database B. By appending @dblink to the name of the table, view, or PL/SQL object in Database B, you can perform operations like SELECT, INSERT, UPDATE, DELETE, and even lock tables.

 Types of Database links

There are two types of database links in Oracle: private and public. The type of link you choose depends on how much security and accessibility you need for the remote data

Private database links 

• Created for a specific user or schema
• Only the user or schema that created the link can access it
• Created using the CREATE PRIVATE DATABASE LINK command

Public database links 

• Accessible to all users and schemas in the local database
• Anyone with the necessary privileges can use it
• Created using the CREATE PUBLIC DATABASE LINK statement 

Oracle Database Link Creation Syntax

CREATE DATABASE LINK <DB link name> CONNECT TO <Username> IDENTIFIED BY <Password> USING <ConnectionString>;

In order to create a database link, you need to provide a name <DB link name> and you need to tell the local database how to connect to the remote one. You have two options:

• Use the same user/password you use locally (this is done with the CURRENT_USER option).
• Provide a specific username (User) and password (Password) for the connection

Lastly, you need a connection string. This can be: 

• A name of an existing entry in your TNSNAMES file. 
• A full connection string if you prefer to specify everything directly.

Here are some "guidelines" I would suggest following before creating a database link:

Before you jump into creating a database link, it's crucial to check with your IT Security department. Make sure you understand the guidelines, Standard Operating Procedures (SOPs), and any standards your organization follows regarding database links. They will let you know if database links are allowed and what specific conditions or restrictions you need to follow. This ensures that you're not only following best practices but also staying compliant with your organization's security policies. 

Make sure there are no firewalls between your local and remote hosts.

Unless it is explicitly permitted and approved, a Database Link from a non-production environment should not be pointed to a Production schema/environment.

Unless it is explicitly permitted and approved, a Database Link from a non-production environment should not be pointed to a Production schema/environment. 

 While creating a private database link you need to provide "CREATE DATABASE LINK" privilege to the schema owner. Make sure it is revoked after the database link creation.