In Oracle, a database link refers to a database object that allows a user to access data on a remote database. It essentially acts as a “link” between two databases, allowing a user to execute a SQL query on one database and access data from another.
Database links are essential for a variety of reasons. For example, they can be used to:
- Consolidate data from multiple databases onto a single server for reporting and analysis purposes.
- Allow users on one database to access data on another database without having to grant them direct access to that database.
- Join data from two different databases in a single SQL query.
This tutorial will teach you how to use Oracle’s CREATE DATABASE LINK command to initialize a new database link.
Oracle Database Links
There are two types of database links in Oracle:
- Private database links – A private database link is owned by a specific user and can only be used by that user. This means that only the link owner can use it to access data on the remote database.
- Public database links – On the other hand, a public database link is owned by the PUBLIC user and can be used by any user in the database. Any user with the necessary privileges can use the link to access data on the remote database.
Private database links are more secure as they are scoped to one specific user. However, public database links provide more accessibility and convenience, as they are global for any user.
It is therefore challenging to set which database you need to use. Carefully consider what is more critical: security or convenience.
Oracle Create Database Link Statement
We use Oracle’s CREATE DATABASE LINK statement to initialize a new database link object.
We can express the statement syntax as shown below:
CONNECT TO username IDENTIFIED BY password
USING 'connect_string';
The statement includes specific parameters, such as:
- SHARED – the SHARED option allows you to create a shared database link that multiple users can access.
- PUBLIC – The PUBLIC option will create a public database link that any user in the database can access.
- CONNECT TO – This option specifies the username and password to connect to the remote database.
- USING – Finally, the USING parameter sets the connection string to connect to the remote database. You can check our tutorial on Oracle Connection String to discover more.
Oracle Create Database Example
The following example will create a database link to connect to a remote Oracle server with the address 192.168.100.23 on port 1521. The remote server has a service name of ORLR.
We will start by adding an entry to the tnsnames.ora file.
If you are not familiar with it, the tnsnames.ora file stores information about the network configuration for an Oracle database. It typically contains entries for each database the Oracle client needs to connect to, along with the necessary connect strings and other details. The Oracle client uses this information to connect to the databases.
This file is typically in the $ORACLE_HOME/network/admin directory. Suppose you’re not sure where the tnsnames.ora file is located on your system; you can try running the following command to find it:
You can also create the file manually if it does not exist.
Add the entry to the target server as shown below:
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.23)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORLR)
)
)
The entry defines a database connection called ORLR, which uses the TCP protocol to connect to the database on the host with the IP address 192.168.100.23 on port 1521.
The CONNECT_DATA section of the entry specifies that the connection uses a dedicated server and that the service name for the database is ORLR. The Oracle client uses this information to connect to the database using this entry.
We can then create a database a database link using the previous entry, as shown below:
CONNECT TO username IDENTIFIED BY password
USING ‘
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.23)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORLR)
)
)
‘;
You could skip the entry definition if you added it to the tnanames.ora file.
Once we have created the link, we can use it to access data on the ORLR database, as shown below:
Conclusion
In this post, you discovered the basics of working with database links in Oracle. Before using database links, it is good to consider the negative impacts that might come with them.
Such potential penalties include:
- Performance – Using a database link can potentially impact the performance of an SQL query. This is because once the query is executed on the database, the data needs to be transferred back to the local database over the network. This can be especially problematic for large queries or frequent queries.
- Security – The second potential impact of database links is security. Using a database link can potentially introduce security risks as it allows users from one database to access data from another. It is, therefore, critical to mind the security of the remote database.
- Dependency – Using a database link can create a dependency on the remote database, making it more challenging to manage a complex environment.
Generally, database links can be a valuable tool in certain situations, but it’s essential to consider the potential negative impacts before implementing them in your cluster.
