Oracle CREATE DATABASE LINK Guide

What is a Database Link?

A Database Link is an Oracle Database feature that allows an Oracle database to connect to a remote database, either Oracle or non-Oracle, and provide a interface for querying them both together. That means you can write one query that pulls data from both the local and remote database servers and joins them together (which is pretty useful).

To make the article clear, I’ll refer to the original Oracle Database as the local database (this is where you create the link). The other database (Oracle or otherwise) I’ll refer to as the remote database.

By using database links, data on the remote database can be queried (either alone or joined to local data), modified, inserted, or even deleted, using standard SQL syntax, all while you are only connected to the local database.

Oracle Database Link Creation Syntax

The simplified syntax for creating a Database Link is shown below, you should consult Oracle documentation for more complex examples.

    CREATE DATABASE LINK <LinkName>
    CONNECT TO [ CURRENT_USER | <User> IDENTIFIED BY <Password> ]
    USING <ConnectionString>;

In order to create a database link, you need to provide a name (LinkName), tell the local database to connect to the remote one using either the same user/password as used locally (with the CURRENT_USER option) or provide a username (User) and password (Password). Besides that, you need to provide a connection string, that can be either a name of an existing entry in TNSNAMES file, or a full connection string.

Example Database Link Using TNSNAMES

In this example, we are creating the SalesLNK database link to access the database defined in the TNSAMES file as ‘SalesDB’ and we are providing the user (RemoteRO) and password (AbCd#1#2) to connect

    CREATE DATABASE LINK SalesLNK
    CONNECT TO RemoteRO IDENTIFIED BY AbCd#1#2
    USING 'SalesDB';

Note : The TNSNAMES entry can point either to an Oracle or non-Oracle database. Oracle offers native connection capabilities for several database engines using either a licensed Database Gateway (eg Sybase, SQL Server, Informix or Teradata) or using the free ODBC Gateway that allows connections to any source with an ODBC driver.

Example Database Link Using A Full Connection String

In this example, we are creating the SalesLNK database link to access a database residing on the sales.acme.com server using TCP port 1521. We are using the same user / password that is currently used to connect to the local database.

    CREATE DATABASE LINK SalesLNK CONNECT TO CURRENT_USER
    USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sales.acme.com)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=SALES)))';

How To Use An Oracle Database Link

Once a database link is created, it can be used to access an object (usually table, but could also be functions, views, etc.) residing on the remote database. We need to add an @ followed by the database link name after any object name to tell Oracle to use the object from the remote link instead of from the local database.

Querying Remote Database Link Tables

The following example shows us how to query a table that resides on a remote database:

    SELECT InvoiceNumber, InvoiceDate, InvoiceAmount
    FROM Invoices@SalesLNK;

Notice the table name is appended with @SalesLnk to tell Oracle that this table is on the remote database identified by the name SalesLink.

Inserting, Updating and Deleting Remote Data Using The Database Link

Although database links are used mostly to query data on a remote databases, Oracle also supports data manipulation statements, as shown below.

Inserting Data

    INSERT INTO Customers@SalesLNK (CustomerName, StatusID, Email, InsertDate)
    VALUES ('John Doe', 1, 'jdoe@mail.com', SYSDATE);

Updating Data

    UPDATE Products@SalesLNK
    SET Price = Price * 1.10
    WHERE Price < 100;

Deleting Data

    DELETE FROM Prospects@SalesLNK
    WHERE Status = 'Inactive';

Executing Remote Code On A Database Link

We can also execute remote procedures or functions from the local database using database links.

    EXEC DeactivateCustomer@SalesLNK (pCustomerID => 10, pReasonID => 1);

When calling a remote procedure or function, we can pass local values as parameters or arguments, but the remote procedure or function can only access data that resides on the remote database.

Querying Both Local And Remote Data Together

There are scenarios where you may need to query data from both local and remote tables in the same query, this is also possible with database links!

Let’s assume that we are working on an inventory database where we have data about products that are sold by our company, and we need a query that returns information about the products that we buy from a particular provider and the date and quantity of items that we have sold.

Let’s pretend that sales information is not stored in our Inventory database, but instead resides in a sales specific database. To deal with this problem we create a SalesLNK database link as shown in the previous section, and can query it together with inventory data.

    SELECT p.ProductID, p.Name, i.InvoiceDate, id.Quantity`
    FROM Products p
    JOIN InvoiceDetail@SalesLNK id ON id.ProductID = p.ProductID
    JOIN Invoices@SalesLNK i ON i.InvoiceID = id.InvoiceID
    WHERE p.ProviderID = 20;

While this is very useful, we may notice that it takes a lot of time to return results, even if there are just a few articles that we sell from provider #20.

Performance Of Database Link Queries

The problem is that since we are querying both local and remote data together, Oracle may decide (based on internal statistics and estimations) to bring a large amount of data from the remote database (for example all records from the Invoices and InvoiceDetail tables) into the local database, and then perform the joining and filtering locally.

Performance problems such as these are a core problem with database links, especially for large datasets.

Optimizing Database Link Queries With DRIVING_SITE

To help solve this problem, we can use the DRIVING_SITE hint to tell Oracle which site (or database) should handle the execution of the query, selecting the one where most of the data resides to reduce traffic between databases and speed up execution.

    SELECT /*+ DRIVING_SITE(id) */ p.ProductID, p.Name, i.InvoiceDate, id.Quantity
    FROM Products p
    JOIN InvoiceDetail@SalesLNK id ON id.ProductID = p.ProductID
    JOIN Invoices@SalesLNK i ON i.InvoiceID = id.InvoiceID
    WHERE p.ProviderID = 20;

In the above example, /*+ DRIVING_SITE(id) */ tells Oracle to execute the query on the site where the table name or alias id resides.

So instead of pulling remote sales data into the local inventory database, we suggest to Oracle that it should send the Products data to the sales database instead. This way, the processing of the query and filtering of the results will happen on the remote database.

Although Oracle optimizer can usually pick the right plan, you may consider using this hint to force execution on one particular site if you notice that performance is not as good as expected. Although figuring out how to do this is certainly more of an art form than a scientific process.

Wrap up

By this point we’ve figured out how to link our local Oracle database to another, remote, database. This is super useful and provides a very convenient environment for querying diverse datasets.

You can use this syntax to create and use database links in Beekeeper Studio, the SQL Developer alternative with a nice, modern UI.