Creating Database Link using Oracle

Database links in Oracle's network topology serve a vital purpose in providing a pathway for connecting and executing distributed database operations across multiple Oracle databases. They enable communication between different physical databases and allow for remote procedure calls and queries in a distributed database system. Using database links, an application can query or modify tables and views, or call PL/SQL procedures in a remote database as if they were local.
To use a database link, you create a link object in the local database that points to a remote database. The remote database can be on the same system or on a completely different system, possibly in a different geographical location.
The basic syntax for creating a database link is:

CREATE [SHARED] [PUBLIC] DATABASE LINK link_name
[CONNECT TO current_user]
[IDENTIFIED BY password]
USING 'connect_string';

Here are the elements of this statement:

1. SHARED: Optional keyword. It allows multiple users to use a single network connection to access the remote database.
2. PUBLIC: Optional keyword. It makes the database link accessible to all users. Without it, the link is private and only available to the user who created it.
3. link_name: This is the name of the database link.
4. CONNECT TO current_user: Optional clause. If included, it means that the same username and password as the local user will be used to connect to the remote database.
5. IDENTIFIED BY password: Optional clause. It specifies the password for the remote username. This clause is only necessary if a remote username is specified.
6. USING 'connect_string': This clause is mandatory and specifies the TNS connect string to access the remote database. The 'connect_string' corresponds to an entry in the tnsnames.ora file.

An example of a database link creation might look like this:

CREATE DATABASE LINK remote_db
CONNECT TO scott IDENTIFIED BY tiger
USING 'orcl';

This statement creates a database link named remote_db, connecting to the orcl database with the username scott and password tiger.
Database links play a critical role in distributed database environments, enabling seamless and efficient data access and management across different databases. As always, it's important to manage database links with careful consideration of security implications, such as safeguarding credentials and limiting the exposure of sensitive data.

After TNS connectivity has been established, database links are used to establish SQL-level connectivity between distributed databases. The first step is to create a hierarchical model to define the topology of your system. For our class project, the model might look like the following:

Database links are essentially transparent to the users of an Oracle distributed database system because the name of a database link is the same as the global name of the database to which the link points. For example, the following SQL statement creates a database link in the local database that describes a path to the remote Raleigh database.

CREATE PUBLIC DATABASE LINK 
raleigh
connect to order_schema_user using secret_password
using ‘RAL’

Remember, the only purpose of a database link is to add a USER_ID and password to the TNS service name so the database will connect transparently. In the statement above, the TNS service name is RAL, and an entry called RAL must exist in the tnsnames.ora file. After creating a database link, applications connected to the local database can access data from the remote Raleigh database.
The USER_ID that is specified in a database link must have the proper authority to perform your requested operations. For example, if you specify a user called CUST_OWNER in the database link, you will only have authority to perform SQL on the tables that are owned by CUST_OWNER, or those tables that have been granted to CUST_OWNER.
In the next lesson, we will look at how to establish connections over database links.