Create Database Link in Oracle

Create Database Link in Oracle

In this article, I am going to discuss Create Database Link in Oracle with Examples. Please read our previous article where we discussed Database Link in Oracle with Examples.

Create Database Link in Oracle

Until now, we understood database links theoretically. Now, let’s go ahead and learn about database links practically. But we do not have too many databases on our server. We have only one database JAYADB i.e. a container database. We are going to create two pluggable databases. Remember that the pluggable databases in 12c and 19c are totally isolated. We can create these two databases without any issues.

Create PROD1 pluggable database:

We will be creating a pluggable database named PROD1 which includes in our database link exercise.

Step 1: Open DBCA.

Open the Linux machine and open the terminal and enter the command “dbca”.

Create Database Link in Oracle with Examples

If you enter the command “dbca” then there will be a popup as above opening the DBCA.

Step 2: Manage Pluggable database

Once the DBCA is opened you will be able to see the below options. Just select the option “manage pluggable database” and click Next.

Create Database Link in Oracle with Examples

Step 3: Create a New pluggable Database

You will see the below options. If you need to create a pluggable database select “create a pluggable database” or if you need to drop a database select “delete pluggable database”. We are selecting “create pluggable database” and clicking Next.

Create Database Link in Oracle with Examples

Step 4: Select the container database.

Once you select the option to create a pluggable database and click next you will get the below page asking to select the container database. Therefore, we have only one container database we will be selecting the container database. We are selecting the JAYADB database. Leave the username and password column. Click Next.

Create Database Link in Oracle with Examples

Step 5: Create a pluggable database from PDB$SEED.

Once you click the next DBCA bring to the below page asking to select the pluggable database. You can select any database but we are using PDB$SEED since it is a template database to create multiple pluggable databases. Click Next.

Create Database Link in Oracle with Examples

Step 6: Pluggable database details.

Once you select the template database and click next the dbca asks you to provide the pluggable database name and administrator username and password for the administrator. We are giving the pluggable database name as PROD1 and username as prod1_admin and password for the user prod1_admin.

Create Database Link in Oracle with Examples

Once you click next you will see a popup that the password doesn’t match the oracle requirements. Ignore the popup and click next.

Create Database Link in Oracle with Examples

Click Yes and continue.

Step 7: Tablespace details and storage type details

Create Database Link in Oracle with Examples

The Storage is a file system and the Database location is the default that is taken by DBCA and then it asks to create the Default user tablespace. Select the option. This is the default option. Click Next.

Step 8: Create a Database Summary.

You can see create database summary which displays the pluggable database name as JAYAPDB2 and the pluggable database source as default and datafile location.

Create Database Link in Oracle with Examples

Click finish and the process of creating pluggable database starts.

Create Database Link in Oracle with Examples

Wait for a few minutes until the process becomes 100%.

Create Database Link in Oracle with Examples

The database is successfully created. Now that the pluggable database PROD1 is created. Let us go ahead and check the status of the database in the database. Connect to the database as “sqlplus / as sysdba”.

Create Database Link in Oracle with Examples

We have connected to the database. Let’s check the list of pluggable databases using the query.

Query: select name, open_mode from v$pdbs;

Create Database Link in Oracle with Examples

Now, that our PROD1 database is running. Let’s go ahead and try to connect to the database.

Create Database Link in Oracle with Examples

We have created a user called prod1_admin. The user prod1_admin does not have any roles. Let us go ahead and grant the dba role to prod1_admin.

Statement: grant dba to prod1_admin;

Create Database Link in Oracle with Examples

We have granted dba access to prod1_admin. The user prod1_admin has all the privileges now. Let us go ahead and create another database named PROD2.

Create PROD2 Pluggable database:

Step 1: Open DBCA.

Open the Linux machine and open the terminal and enter the command “dbca”.

Create Database Link in Oracle with Examples

If you enter the command “dbca” then there will be a popup as above opening the DBCA.

Step 2: Manage Pluggable database

Once the DBCA is opened you will be able to see the below options. Just select the option “manage pluggable database” and click Next.

Create Database Link in Oracle with Examples

Step 3: Create a New pluggable Database

You will see the below options. If you need to create a pluggable database select “create a pluggable database” or if you need to drop a database select “delete pluggable database”. We are selecting “create pluggable database” and clicked Next.

Create Database Link in Oracle with Examples

Step 4: Select the container database.

Now, we will get the below page asking to select the container database. Therefore, we have only one container database we will be selecting the container database. We are selecting the JAYADB database. Leave the username and password column. Click Next. Create Database Link in Oracle with Examples

Step 5: Create a pluggable database from PDB$SEED.

Once you click the next DBCA bring to the below page asking to select the pluggable database. You can select any database but we are using PDB$SEED since it is a template database to create multiple pluggable databases. Click Next.

Create Database Link in Oracle with Examples

Step 6: Pluggable database details.

Once you select the template database and click next the dbca asks you to provide the pluggable database name and administrator username and password for the administrator. We are giving the pluggable database name PROD2 and username as prod2_admin and password for the user prod2_admin.

Create Database Link in Oracle with Examples

Once you click next you will see a popup that the password doesn’t match the oracle requirements. Ignore the popup and click next.

Create Database Link in Oracle with Examples

Click Yes and continue.

Step 7: Tablespace details and storage type details

The Storage is a file system and the Database location is the default that is taken by DBCA and then it asks to create the Default user tablespace. Select the option. This is the default option. Click Next.

Create Database Link in Oracle with Examples

Step 8: Create a Database Summary.

You can see create database summary which displays the pluggable database name as JAYAPDB2 and the pluggable database source as default and datafile location.

Create Database Link in Oracle with Examples

Click finish and the process of creating pluggable database starts.

Create Database Link in Oracle with Examples

Wait for a few minutes until the process becomes 100%.

Create Database Link in Oracle with Examples

The database is successfully created. Now that the pluggable database PROD2 is created. Let us go ahead and check the status of the database in the database. Connect to the database as “sqlplus / as sysdba”.

Create Database Link in Oracle with Examples

We have connected to the database. Let us check the list of pluggable databases using the query.

Query: select name,open_mode from v$pdbs;

Create Database Link in Oracle with Examples

Now, that our PROD2 database is running. Let us go ahead and try to connect to the database. We have created a user called prod1_admin. The user prod1_admin does not have any roles. Let us go ahead and grant the dba role to prod1_admin.

Statement: grant dba to prod1_admin;

Create Database Link in Oracle with Examples

We have granted dba access to prod1_admin. The user prod1_admin has all the privileges now. Let us go ahead and create another database named PROD2.

Create TNS Entry:

Now, that we have created two new pluggable databases. Let us go ahead and add the TNS entry of these two databases in tnsnames.ora file. Open tnsnames.ora file

Create Database Link in Oracle with Examples

Now, let us go ahead and copy the pluggable database JAYAPDB entry and paste it into tnsnames.ora file. Then we have to make changes to PROD1 and PROD2 database details as below.

We have added PROD1 tns entry with tnsname as PROD1 and service name as prod1.com. We have done the same for the PROD2 database with PROD2 as tns name and the service name as prod2.com. As we have added the entry in tnsnames.ora file. Let’s check the listener status if these two services are registered or not.

Create Database Link in Oracle with Examples

We can see the services prod1.com and prod2.com are present and the status of those two services is READY. Let us go ahead and try to connect to these pluggable databases PROD1 and PROD2 using tnsnames in client style.

Connection to PROD1:

Create Database Link in Oracle with Examples

Connection to PROD2:

Create Database Link in Oracle with Examples

From our example, we have created the two pluggable databases PROD1 and PROD2, and the users prod1_admin and prod2_admin. We have to create employees table in prod2_admin user. Let us go ahead and create the employees table in the PROD2 database in the prod2_admin user.

Statement: create table employees (id number, name varchar2(100));

Create Database Link in Oracle with Examples

Now, that the table is created let us go ahead and insert one record into the table employees. After inserting we have to commit the data.

Statement: insert into employees(1,’jaya’);

Create Database Link in Oracle with Examples

Let us check if the table is present or not using the query.

Query: select * from employees;

So, we have created the prod1 and prod2 databases and we have created prod1_admin and prod2_admin users. We have created a table called employees and inserted a record in the table. The step that is left is creating the database link. In order to create a database link, we have to connect to the PROD1 database.

Command: sqlplus prod1_admin/goodluck@prod1

Create Database Link in Oracle with Examples

Now, that we have connected to the PROD1_ADMIN user in the PROD1 database. Let us go ahead and create the database link.

Create database link:

Create database link read_prod2 connect to prod2_admin identified by goodluck using ‘prod2’;

Create Database Link in Oracle with Examples

We can see the database link is created. But we have created the private database link. A private database link indicates only the user prod1_admin can access the database link. Public database links can be accessed by all the users in the database.

How to use the database link?

In order to read the table in the prod2 database we have to use the database link. To use the database, we have to run the below query.

Query: select * from employees@read_prod2;

Create Database Link in Oracle with Examples

We can see the output as the same in the prod2 database. The query that we tried using the database link has worked and the output has been retrieved successfully. So, @read_prod2 will connect to the prod2 database and extract the table from prod2 and display the tables to us.

The user prod1_admin can access any tables in prod2_admin. The prod1_admin can insert, create, and delete tables in the prod2_admin user which is present in the prod2 database. For more information about the database links, we can go ahead and check the view dba_db_links.

Command: desc dba_db_links

Create Database Link in Oracle with Examples

In the next article, I am going to discuss Controlling User Access in Oracle with Examples. Here, in this article, I try to explain Create Database Link in Oracle with Examples and I hope you enjoy this Create Database Link in Oracle with Examples article.

Leave a Reply

Your email address will not be published. Required fields are marked *