A database link may be a connection from the Oracle database to a different remote database. The remote database are often an Oracle Database or any ODBC compliant database like SQL Server or MySQL.

Why does one need a database link

A database link allows a user or program to access database objects like tables and views from another database.

Once you create a database link, you’ll access the tables or views from the remote database using the subsequent pattern:

table_name@database_link

For example, you’ll query data from a table within the remote database as if it had been within the local server:

SELECT * FROM remote_table@database_link;

When accessing a foreign table or view over the database link, the Oracle database is acting as an Oracle client.

Using a synonym to simplify the syntax for accessing objects via a database link

To simplify the syntax, you’ll create a synonym for the remote object accessed via the database link and use this object as if it had been an area object.

This syntax shows the way to create a synonym for a foreign table:

CREATE SYNONYM local_table
FOR remote_table@database_link;

And this question uses the synonym rather than the remote table name with the database link:

SELECT * FROM local_table;

Oracle CREATE DATABASE LINK statement

There are two sorts of database links: public and personal .

Private database links are visible to the owners while public database links are visible to all or any users within the database. For this reason, public database links may pose some potential security risks.

To create a personal database link, you employ the CREATE DATABASE LINK statement as follows:

CREATE DATABASE LINK dblink
CONNECT TO remote_user IDENTIFIED BY password
USING 'remote_database';

In this syntax:

First, specify the name of the database link after the CREATE DATABASE LINK keywords.
Second, provide user and password of the remote database after the hook up with and IDENTIFIED BY keywords.
Finally, specify the service name of the remote database. If you specify only the database name, Oracle will append the database domain to the connect string to make an entire service name.

Typically, you add an entry int the tnsnames.ora file and reference it because the remote_database within the USING clause.

The following statement shows the way to create the private database link to a user during a remote database with a full connection string.

CREATE DATABASE LINK dblink

hook up with remote_user IDENTIFIED BY password

 USING '(DESCRIPTION=
 (ADDRESS=(PROTOCOL=TCP)(HOST=oracledb.example.com)(PORT=1521))
 (CONNECT_DATA=(SERVICE_NAME=service_name))
 )';

To create a public database link, just add the general public keyword:

CREATE PUBLIC DATABASE LINK dblink

hook up with remote_user IDENTIFIED BY password

 USING 'remote_database';

Create a database link example

In this example, we’ll create a database link to a foreign Oracle Database server located within the server 10.50.100.143 with the port 1521 and repair name SALES.

First, add the subsequent entry to tnsnames.ora enter the local Oracle Database server. Typically, the tnsnames.ora is found within the directory /NETWORK/ADMIN/ under ORACLE_HOME:

SALES =
(DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = 10.50.100.143)(PORT = 1521))
 (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SERVICE_NAME = SALES_PRD)
 )
)

Next, use the CREATE DATABASE LINK statement to make a replacement private database link that connects to the SALES database via bob‘s account:

CREATE DATABASE LINK sales

hook up with bob IDENTIFIED BY Abcd1234

 USING 'SALES';

Then, issue the SELECT statement to question data from the purchasers table on the SALES database:

SELECT * FROM customers@sales;

Here is that the output:

After that, insert a replacement row into the purchasers table:

INSERT INTO customers@sales(customer_id, name, email)

VALUES(2,’XYZ Inc’,’contact@xyzinc.com’);

Finally, query data from the purchasers table again:

 SELECT * FROM customers@sales

The result set is as follows:

Oracle Database Link best practices

Here are some best practices using the database links:

Naming convention: the name of the database links should reflect the character of knowledge , not the database server. for instance , rather than naming a database link SALES_PRD, you name it as SALES.
Remote database users: you ought to create a user dedicated for a database link. additionally , you ought to not give this user to anyone else. If you don’t follow this, the database won’t work when someone changes the password of the user or maybe delete it.
Use a service-specific entry within the tnsnames.ora rather than the database-specific alias in order that you copy between product, test, and development environments, you don’t need to recreate the database link.

So, this brings us to the end of blog. This Tecklearn ‘How to Create Oracle Database Links’ blog helps you with commonly asked questions if you are looking out for a job in Oracle DB. If you wish to learn Oracle DB and build a career in Database domain, then check out our interactive, Oracle 12c DBA Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

https://www.tecklearn.com/course/oracle-12c-dba-course/

Oracle 12c DBA Course Training

About the Course

Oracle Database Administration training will help you master the Oracle DBA. As part of the training, you will master the Oracle Database architecture, the database processes, memory structures, schema objects, security, data recovery and backup through hands-on projects and case studies. Oracle DBA Training helps students gain the skills and knowledge required to install, configure, and administer Oracle databases. Besides, this training also develops the full skill set required to become a successful Oracle Database Administrator.

Why Should you take Oracle DBA Training?

The Average salary of an Oracle DBA is $108,837 per annum – GlassDoor.com
According to Gartner, Oracle is the market leader in the Relational Database with 48.35% market share globally.
IBM, TCS, Tech Mahindra, Oracle, Wipro & other MNCs worldwide use Pl-SQL for their database deployments.

What you will Learn in this Course?

Introduction to Oracle Database

What is the Oracle Database
Pre-requisites for oracle database
Oracle database 12c installation
Multi-tenant architecture

Exploring the Oracle 12c Database Architecture

Explain the Memory Structures
Describe the Process Structures
Overview of Storage Structures

Preparing the Database Environment

Identify the tools for Administering an Oracle Database
Plan an Oracle Database Installation
Install the Oracle Software by using Oracle Universal Installer (OUI)
Create a Database by using the Database Configuration Assistant (DBCA)

Managing the Oracle Instance

Setting database initialization parameters
Describe the stages of database startup and shutdown

Database Storage Structures and Schema Objects

Managing Database Storage Structures
Overview of table space and data files
Overview of Primary Components
Identify the importance of checkpoints, redo log files, and archived log files
Configure ARHIVELOG mode

Database Startup & User Requests and Oracle Network Environment

Describe the stages of database startup and shutdown
Using alert log and trace files
Configure and Manage the Oracle Network
pFile and spFile
Tablespace Management
Storage and Relationship Structure

Oracle Backup & Recovery

Redo log File Management
Performing Database Recovery
Overview of Data Recovery Advisor
Use Data Recovery Advisor to Perform recovery (Control file, Redo log file and Data file)

Deep Dive into Oracle Recovery Manager (RMAN)

Using the RMAN Recovery Catalog
Identify situations that require RMAN recovery catalog
Create and configure a recovery catalog
Synchronize the recovery catalog
Create and Use RMAN stored scripts
Back up the recovery catalog
Create and use a virtual private catalog

Configuring Backup Specifications

Using RMAN to Create Backups
Create image file backups
Create a whole database backup
Enable fast incremental backup
Using RMAN to Perform Recovery
Perform complete recovery from a critical or noncritical data file loss using RMAN
Perform incomplete recovery using RMAN

Data Movement and Oracle Data Pump

Moving Data
Describe and use methods to use move data (Directory objects, SQL* Loader, External tables)
Explain the general architecture of Oracle Data Pump

Data Dictionary & Dynamic Performance Tables

Oracle Data Dictionary contains information related to database privileges
Data dictionary views
Media failure
Flashback CDB

Database Tuning

What is database tuning
How to tune the database
Tuning the data dictionary

Got a question for us? Please mention it in the comments section and we will get back to you.

456