Creating Database Links in Oracle AWS RDS: A Comprehensive Guide In today's interconnected data landscape, the ability to seamlessly access data across different databases is crucial. Oracle Database Links provide this capability, allowing you to query and manipulate data residing in remote Oracle databases as if they were local tables. This article will guide you through the process of creating database links in Oracle AWS RDS, focusing on accessibility for both public and current users. Introduction As a database professional, I often encounter scenarios where accessing data across different Oracle databases is necessary. Oracle Database Links help me to avoid cumbersome data transfers and maintain data consistency by enabling real-time access to remote data. In this guide, I'll share my knowledge and provide practical examples to help you set up database links in your Oracle AWS RDS environment. Scenario Imagine you have an application running on an Oracle Autonomous DB in the cloud. This application needs to access product information stored in a legacy Oracle database hosted on AWS RDS. Instead of migrating the data, you can create a database link in the Autonomous DB to access the product data directly from the RDS instance. This approach minimizes downtime, reduces storage costs, and ensures that your application always has access to the latest product information. How to Setup a Database Link in Oracle AWS RDS Below are the steps to setup database link in Oracle AWS RDS. Gather Connection Information: You'll need the following information for the remote database:

Hostname or IP address of the RDS instance.
Port number (typically 1521).
Service name or SID of the database.
Username and password for a database user with appropriate privileges.



Create the Database Link: Connect to your Oracle database (e.g., using SQL Developer or SQL*Plus) and use the CREATE DATABASE LINK statement. Here's a basic example:
CREATE DATABASE LINK rds_link
CONNECT TO remote_user IDENTIFIED BY "password"
USING '(DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rds_endpoint)(PORT = 1521))
      (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )';


Replace rds_link, remote_user, password, rds_endpoint, and orcl with your actual values.


Test the Database Link: Verify the connection by querying a table in the remote database:
SELECT * FROM products@rds_link;

If the query executes successfully, your database link is working! Gather Connection Information: You'll need the following information for the remote database:

Hostname or IP address of the RDS instance.
Port number (typically 1521).
Service name or SID of the database.
Username and password for a database user with appropriate privileges. Gather Connection Information: You'll need the following information for the remote database: Gather Connection Information: Hostname or IP address of the RDS instance.
Port number (typically 1521).
Service name or SID of the database.
Username and password for a database user with appropriate privileges. Hostname or IP address of the RDS instance. Port number (typically 1521). Service name or SID of the database. Username and password for a database user with appropriate privileges. Create the Database Link: Connect to your Oracle database (e.g., using SQL Developer or SQL*Plus) and use the CREATE DATABASE LINK statement. Here's a basic example:
CREATE DATABASE LINK rds_link
CONNECT TO remote_user IDENTIFIED BY "password"
USING '(DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rds_endpoint)(PORT = 1521))
      (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )';


Replace rds_link, remote_user, password, rds_endpoint, and orcl with your actual values. Create the Database Link: Connect to your Oracle database (e.g., using SQL Developer or SQL*Plus) and use the CREATE DATABASE LINK statement. Here's a basic example: Create the Database Link: CREATE DATABASE LINK CREATE DATABASE LINK rds_link
CONNECT TO remote_user IDENTIFIED BY "password"
USING '(DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rds_endpoint)(PORT = 1521))
      (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )'; CREATE DATABASE LINK rds_link
CONNECT TO remote_user IDENTIFIED BY "password"
USING '(DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rds_endpoint)(PORT = 1521))
      (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )'; Replace rds_link, remote_user, password, rds_endpoint, and orcl with your actual values. rds_link remote_user password rds_endpoint orcl Test the Database Link: Verify the connection by querying a table in the remote database:
SELECT * FROM products@rds_link;

If the query executes successfully, your database link is working! Test the Database Link: Verify the connection by querying a table in the remote database: Test the Database Link: SELECT * FROM products@rds_link; SELECT * FROM products@rds_link; If the query executes successfully, your database link is working! How to Setup db link for Public To make the database link accessible to all users, you can create a public synonym. This allows any user to access the remote data without needing to know the database link name. CREATE PUBLIC SYNONYM products_public FOR products@rds_link; CREATE PUBLIC SYNONYM products_public FOR products@rds_link; Now, any user can query the remote products table using: SELECT * FROM products_public; SELECT * FROM products_public; If you want to grant access to the database link to specific users only, you can grant them the CREATE SESSION privilege on the remote database and then create a private synonym for each user. CREATE SESSION Grant CREATE SESSION Privilege: Connect to the remote database as an administrator and grant the CREATE SESSION privilege to the desired user:
GRANT CREATE SESSION TO user1;



Create Private Synonym: Connect to the user's schema (e.g., as user1) and create a private synonym:
CREATE SYNONYM products_private FOR products@rds_link;

Now, only user1 can access the remote products table using:
SELECT * FROM products_private; Grant CREATE SESSION Privilege: Connect to the remote database as an administrator and grant the CREATE SESSION privilege to the desired user:
GRANT CREATE SESSION TO user1; Grant CREATE SESSION Privilege: Connect to the remote database as an administrator and grant the CREATE SESSION privilege to the desired user: Grant CREATE SESSION Privilege: CREATE SESSION GRANT CREATE SESSION TO user1; GRANT CREATE SESSION TO user1; Create Private Synonym: Connect to the user's schema (e.g., as user1) and create a private synonym:
CREATE SYNONYM products_private FOR products@rds_link;

Now, only user1 can access the remote products table using:
SELECT * FROM products_private; Create Private Synonym: Connect to the user's schema (e.g., as user1) and create a private synonym: Create Private Synonym: user1 CREATE SYNONYM products_private FOR products@rds_link; CREATE SYNONYM products_private FOR products@rds_link; Now, only user1 can access the remote products table using: user1 SELECT * FROM products_private; SELECT * FROM products_private; Grants and Privileges Required to Access DB Link To ensure secure and controlled access to the database link, the following grants and privileges are essential: On the Remote Database: On the Remote Database: Privilege

Description



CREATE SESSION

Allows the database link user to connect to the remote database.This is a minimum requirement.



SELECT on specific tables/views

Grants the database link user the privilege to select data from specific tables or views in the remote database. Replace "table_name" with actual table names.



EXECUTE on specific procedures/functions

Grants the database link user the privilege to execute functions or procedures in the remote database.Replace "procedure_name" with actual procedure names. Privilege

Description



CREATE SESSION

Allows the database link user to connect to the remote database.This is a minimum requirement.



SELECT on specific tables/views

Grants the database link user the privilege to select data from specific tables or views in the remote database. Replace "table_name" with actual table names.



EXECUTE on specific procedures/functions

Grants the database link user the privilege to execute functions or procedures in the remote database.Replace "procedure_name" with actual procedure names. Privilege

Description Privilege Privilege Description Description CREATE SESSION

Allows the database link user to connect to the remote database.This is a minimum requirement. CREATE SESSION CREATE SESSION CREATE SESSION Allows the database link user to connect to the remote database.This is a minimum requirement. Allows the database link user to connect to the remote database.This is a minimum requirement. SELECT on specific tables/views

Grants the database link user the privilege to select data from specific tables or views in the remote database. Replace "table_name" with actual table names. SELECT on specific tables/views SELECT on specific tables/views SELECT Grants the database link user the privilege to select data from specific tables or views in the remote database. Replace "table_name" with actual table names. Grants the database link user the privilege to select data from specific tables or views in the remote database. Replace "table_name" with actual table names. EXECUTE on specific procedures/functions

Grants the database link user the privilege to execute functions or procedures in the remote database.Replace "procedure_name" with actual procedure names. EXECUTE on specific procedures/functions EXECUTE on specific procedures/functions EXECUTE Grants the database link user the privilege to execute functions or procedures in the remote database.Replace "procedure_name" with actual procedure names. Grants the database link user the privilege to execute functions or procedures in the remote database.Replace "procedure_name" with actual procedure names. On the Local Database: On the Local Database: No specific grants are required on the local database for the database link to function. However, users who need to access the database link need the privilege to select from specific tables/views Usage & Examples Let's illustrate with a practical example. Assume you have a table named Customers in the remote database. Customers Querying Data: Querying Data: SELECT * FROM Customers@rds_link WHERE customer_id = 123; SELECT * FROM Customers@rds_link WHERE customer_id = 123; Joining Tables: Joining Tables: SELECT o.order_id, c.customer_name
FROM Orders o
JOIN Customers@rds_link c ON o.customer_id = c.customer_id; SELECT o.order_id, c.customer_name
FROM Orders o
JOIN Customers@rds_link c ON o.customer_id = c.customer_id; Inserting Data: Inserting Data: INSERT INTO Customers@rds_link (customer_id, customer_name) VALUES (456, 'New Customer'); INSERT INTO Customers@rds_link (customer_id, customer_name) VALUES (456, 'New Customer'); Conclusion Creating and managing database links in Oracle AWS RDS is a straightforward process that can significantly improve data accessibility and integration. By understanding the different access options (public vs. private) and the necessary grants and privileges, you can ensure secure and efficient data access across your Oracle environments. This approach avoids cumbersome data transfers and maintains data consistency by enabling real-time access to remote data.

This story contains new, firsthand information uncovered by the writer.

The code in this story is for educational purposes. The readers are solely responsible for whatever they build with it.

Walkthroughs, tutorials, guides, and tips. This story will teach you how to do something new or how to do something better.

Creating DB Links in Oracle AWS RDS: A Comprehensive Guide

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

Untitled Story

Database Health Check: Understanding and Tuning Core Server Resources

10 Features Where Coda Is Better Than Notion

7 Key Features Of The Ideal Collaboration Tool

Great News: WhatsApp Finally Let’s You Text Yourself

[HTML5] Review : the good parts

Database Health Check: Understanding and Tuning Core Server Resources

10 Features Where Coda Is Better Than Notion

7 Key Features Of The Ideal Collaboration Tool

Great News: WhatsApp Finally Let’s You Text Yourself

[HTML5] Review : the good parts

Light-Mode

Classic

Newspaper

Dark-Mode

Neon Noir

Minty

HN StartUps