Explain the purpose and syntax of Oracle database links.
Creating Database Link using Oracle
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:
Topology model
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’
Purpose of Database link
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.