Oracle Create Database Link
Tags: oracle
Published: 2023-11-09 08:00:00

**In Oracle, a database link is a pathway that enables the transfer of data between two separate databases. This allows users to access remote data as if it were stored locally, making it a valuable tool for organizations that need to centralize their data management and share information between different systems and departments.**

---
Tools used in this tutorial
[DbVisualizer](https://www.dbvis.com/download/), top-rated database management tool and SQL client
The [Oracle](https://www.oracle.com/se/database/technologies/oracle-database-software-downloads.html) database 

---
DbVisualizer is a user-friendly database management software with a graphical interface for working with databases, including Oracle. One of its many capabilities is the ability to create and manage database links, making it an efficient and convenient tool for performing operations that make use of this pathway.

This article will guide you through the process of creating a database link in Oracle using DbVisualizer, including important tips and tricks to help you avoid common issues and make the most of this powerful tool.

## What is a Database Link in Oracle?
In Oracle, a database link is a connection that allows users to access data from a remote database as if it were stored locally. It acts as a bridge between two databases and eliminates the need for copying data to multiple locations. All database links are identified by a unique name and can be accessed by prefixing the table name with the link name. They simplify data management by allowing centralized access to remote data and sharing information between different systems and departments.

## Why are Database Links Important?
Database links play a crucial role in modern organizations for several reasons:

- **Central Data Control:** By connecting different databases, database links allow for central data management, ensuring its accuracy and consistency.
- **Information Exchange:** Database links make it possible to share information between different systems and departments without having to copy data to multiple locations physically.
- **Streamlined Operations:** By allowing users to access remote data as if it were stored locally, database links simplify data management and enhance operational efficiency.
- **Better Collaboration:** Database links facilitate collaboration by enabling real-time information sharing between different systems and departments.
- **Scalable Solution:** Database links provide a scalable solution for organizations that need to manage and access large amounts of data stored in multiple locations.

## Types of Database Links in Oracle
In Oracle, there are two types of database links: private and public.

Private links are created for a specific user or schema and can only be accessed by that user or schema. It is established using the `CREATE PRIVATE DATABASE LINK` command.

Public links are accessible to all users and schemas in the local database and can be used by anyone with the necessary privileges.

The choice between private and public links depends on the level of security and accessibility required for remote data. It is set up using the `CREATE PUBLIC DATABASE LINK` statement.

## Creating a Database Link in Oracle with DbVisualizer
Creating a database link in Oracle using DbVisualizer is a simple and straightforward process. By following a step-by-step guide, you can easily establish a connection between two separate databases, allowing you to access remote data as if it were stored in your local database.

### Step 1 - Installation of Oracle Database
First, we need to download the Oracle database on our local machine [from the Oracle website](https://www.oracle.com/ae/database/technologies/oracle19c-windows-downloads.html).
<br />
![Open the installation.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fulowmcteqg3nk498xq6.png)
<figure><figcaption>Open the installation.</figcaption></figure>
<br />

After downloading, open the installation and set the username and password for the database.
<br />
![Set the username and password for the database.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ito0hu1md3q2kt0r4c2y.png)
<figure><figcaption>Set the username and password for the database.</figcaption></figure>
<br />

The installation has been completed successfully.

<br />
![The installation has been completed successfully.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4rbkr2kj3sa1pw9tifvt.png)
<figure><figcaption>The installation has been completed successfully.</figcaption></figure>
<br />

### Step 2 - Installation of DbVisualizer and JDK 17
As a prerequisite, [download JDK 17 from the Oracle platform for DbVisualizer](https://www.oracle.com/java/technologies/javase/jdk17-archive-downloads.html). Make sure to install JDK after carefully choosing your OS version.

<br />
![Install JDK after carefully choosing your OS version.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/v6cv9jrubzhafojx2qiw.png)
<figure><figcaption>Install JDK after carefully choosing your OS version.</figcaption></figure>
<br />
After downloading JDK, install and verify it using the following command in CLI.

```
$ java --version
```

<br />
![Install and verify the version using the following command in CLI.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/000ck391lmvj3eh1uky9.png)
<figure><figcaption>Install and verify the version using the following command in CLI.</figcaption></figure>
<br />

Now go to the following link and download [DbVisualizer](https://www.dbvis.com/download/).

<br />
![Download DbVisualizer.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/f94n4qtxlmtystgblmg0.png)
<figure><figcaption>Download DbVisualizer.</figcaption></figure>
<br />
After downloading, open the installation and install DbVisualizer. Select whether you want to associate .sql file types with DbVisualizer making them open in the tool here:

<br />
![Select whether you want to associate .sql file types with DbVisualizer.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1gkj3nwq7j6ucs7nmse3.png)
<figure><figcaption>Select whether you want to associate .sql file types with DbVisualizer.</figcaption></figure>
<br />

Click Next, and you will see that DbVisualizer has been successfully installed.

<br />
![DbVisualizer has been successfully installed.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yd3x8l5qjyl43ul2i1lt.png)
<figure><figcaption>DbVisualizer has been successfully installed.</figcaption></figure>
<br />

### Step 3 - Creating a Connection with Oracle through DbVisualizer
Now we will create a new connection with the Oracle database using DbVisualizer. Click on the highlighted plus icon and select the “Oracle 9i” option.
<br />
![Create a new connection with the Oracle database using DbVisualizer.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ureqdhrtku5wlobko375.png)
<figure><figcaption>Create a new connection with the Oracle database using DbVisualizer.</figcaption></figure>
<br />
Now enter the username as “SYS” and Oracle DB password, choose the SYS Role as “SYSDBA,” and then click on the “Connect” button.

<br />
![Enter the username as “SYS” and Oracle DB password and connect.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/u3jdgtjkdb95znk0fuln.png)
<figure><figcaption>Enter the username as “SYS” and Oracle DB password and connect.</figcaption></figure>
<br />
Now the connection has been established.

<br />
![The connection has been established.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dl7fd2rn2zti9qjsvft5.png)
<figure><figcaption>The connection has been established.</figcaption></figure>
<br />

### Step 4 - Get Pluggable Database and Set Container
To experience the behavior of database links in Oracle, we have to do some prerequisites, so get the name of our pluggable database by the following query.

```
1 SELECT PDB_NAME FROM DBA_PDBS;
```

<br />
![“ORCLPDB” is the name of our pluggable database.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6fvf3z9nfu8vva4lo8ri.png)
<figure><figcaption>“ORCLPDB” is the name of our pluggable database.</figcaption></figure>
<br />
ORCLPDB is the name of our pluggable database. Use it according to your settings by using the above query. Now use the alter session command and set the container with a pluggable database name.

```
1 ALTER SESSION SET CONTAINER = ORCLPDB;
```

<br />
![Pluggable database has been set.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/l2i1xp0xmkmkcihrijt7.png)
<figure><figcaption>Pluggable database has been set.</figcaption></figure>
<br />


### Step 5 - Create Users and Grant Access in the Current Container
Here, we will create 2 users that will display the behavior of 2 databases by which we can visualize the database link usage.

- Create “User 1” and Grant Access. Run the following query to create “user 1” in the current container.

```
1 CREATE USER user1 IDENTIFIED BY user1_password DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON users CONTAINER = CURRENT;
```

<br />
![Create “User 1” and Grant Access.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ugwiw4efbhi0to5zp8ax.png)
<figure><figcaption>Create “User 1” and Grant Access.</figcaption></figure>
<br />

Now grant necessary privileges to “user 1”.

```
1 GRANT CONNECT, RESOURCE, DBA TO user1;
```

<br />
![Grant necessary privileges to “user 1”.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5gmae0osuyp0q8bmvkyz.png)
<figure><figcaption>Grant necessary privileges to “user 1”.</figcaption></figure>
<br />

- Create “User 2” and Grant Access. Run the following query to create “user 2” in the current container.

```
1 CREATE USER user2 IDENTIFIED BY user2_password DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON users CONTAINER = CURRENT;
```

<br />
![Create “User 2” and Grant Access.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yqzl1b7mbrc4ogrgo7m0.png)
<figure><figcaption>Create “User 2” and Grant Access.</figcaption></figure>
<br />

Now grant necessary privileges to “user 2”.

```
1 GRANT CONNECT, RESOURCE, DBA TO user2;
```

<br />
![Grant necessary privileges to “user 2”.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/a1jzb4sqebb1rtl5uz6y.png)
<figure><figcaption>Grant necessary privileges to “user 2”.</figcaption></figure>
<br />

Great! You’ve now created users and granted some of the privileges to these users. Now it’s time to create a database link.

### Step 6 - Oracle Create Database Link
Now connect to “user 1” using DbVisualizer, and create a database link to “user 2”.

```
1 CREATE DATABASE LINK link_to_user2
2 CONNECT TO user2 IDENTIFIED BY user2_password
3 USING 'localhost:1521/ORCLPDB';
```
 
<br />
![Connect to “user 1” and create a database link to “user 2”.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/l7tm5tzkoly66bezn64g.png)
<figure><figcaption>Connect to “user 1” and create a database link to “user 2”.</figcaption></figure>
<br />



### Step 7 - Create Database Tables and Insert Data
Create a table that will be associated with the first user (user1) and add one data row to it.

```
1 CREATE TABLE user1_table (id NUMBER, name VARCHAR2(100));
2 INSERT INTO user1_table VALUES (1, 'John Doe');
```

<br />
![Create a database table.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/oe2mpmiu4tmxttywgl1e.png)
<figure><figcaption>Create a database table.</figcaption></figure>
<br />

Now create a table that will be associated with the second user (user2) and add one data row to it.

```
1 CREATE TABLE user2_table (id NUMBER, name VARCHAR2(100));
2 INSERT INTO user2_table VALUES (1, 'Jane Smith');
```

<br />
![Create a table that will be associated with the user 2.
](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/f6wh5s4f8kf5w367izj0.png)
<figure><figcaption>Create a table that will be associated with the user 2.</figcaption></figure>
<br />

### Step 8 - Test the Oracle Database Link
After integrating and setting the Oracle database link, run the following query to test everything.

```
1 SELECT * FROM user2_table@link_to_user2;
```
 
<br />

![Run the following query.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wclp62s9yvnmtk53ewx6.png)
<figure><figcaption>Run the following query.</figcaption></figure>
<br />

Finally the user has access to the data from another database using the database link functionality.

## Conclusion
In this article, we delved into the process of creating a database link in Oracle using [DbVisualizer](https://www.dbvis.com/eval/). We emphasized the significance of database links for centralizing data management and enabling information sharing between different systems and departments.

The steps involved in the process, prerequisites, and tips for troubleshooting common issues were also discussed. By using DbVisualizer, the process of creating a database link in Oracle becomes simpler and more efficient because of its abilities to run queries, simplify your workflow, and having specific features each of them designed to solve real-world problems. With a clear understanding of the steps involved and the troubleshooting tips provided, you can successfully create a database link in Oracle and take advantage of its benefits.

[Visit our TheTable blog](https://www.dbvis.com/thetable/) and stay tuned to learn more about the database world, and until next time.


## About the author
Igor Bobriakov is an entrepreneur and educator with a background in marketing and data science. His passion is to help small businesses and startups to utilize modern technology for growth and success. Igor is Data Scientist and ML Engineer. He founded Data Science School in 2016 and wrote numerous award-winning articles covering advanced tech and marketing topics. You can connect with him on LinkedIn or Twitter.