How to Create DB Link for Oracle ADB? - ABOTTS INC
Abotts Logo Abotts Partners with singapore based tech giant to help migrate their public sector customer from Sybase to SQL server.
Upworks Logo Abotts successfully decouples and migrates Upwork EBS custom schema to oracle cloud.
Abotts Logo ABOTTS partners with County in Los Angeles to upgrade their court infrastructure into new technologies.
Upworks Logo Upworks Inc partners with ABOTTS to build their Oracle Cloud Infrastructure (OCI) and migrate their custom applications to OCI.
QuinStreet Logo QuinStreet partners with Abotts to archive and manage their IT systems on Oracle cloud (OCI).
Gnorth Logo Abotts Inc Partners with Gnorth consulting to deploy exadata and ODA for a large public sector customer.
Abotts Logo Abotts Partners with singapore based tech giant to help migrate their public sector customer from Sybase to SQL server.
Upworks Logo Abotts successfully decouples and migrates Upwork EBS custom schema to oracle cloud.
Abotts Logo ABOTTS partners with County in Los Angeles to upgrade their court infrastructure into new technologies.
Upworks Logo Upworks Inc partners with ABOTTS to build their Oracle Cloud Infrastructure (OCI) and migrate their custom applications to OCI.
QuinStreet Logo QuinStreet partners with Abotts to archive and manage their IT systems on Oracle cloud (OCI).
Gnorth Logo Abotts Inc Partners with Gnorth consulting to deploy exadata and ODA for a large public sector customer.

How to Create DB Link for Oracle ADB?

How to Create a Database Link for Oracle Autonomous Database (ADB)?

Creating a database link (DB Link) allows Oracle Autonomous Database to connect to an on-premises or remote Oracle database. Here are the steps to get you started:

Prerequisites

  1. Ensure Accessibility: Verify that the on-premises or remote database is accessible from the Oracle Autonomous Database.
  2. Obtain Database Credentials: Acquire the necessary database credentials for accessing the remote database.
  3. Verify user privileges: Confirm that the user you are using has been granted the CREATE DATABASE LINK privilege. If not, provide the access using the following query:GRANT CREATE DATABASE LINK TO DB_USER;
  4. Confirm Firewall Configuration: Ensure that the appropriate firewall rules are configured for both databases to facilitate secure communication.

Steps to Create a Database Link from Oracle Autonomous Database to On-Premises or Remote Oracle Database

Step 1: Use the DBMS_CLOUD Package to Create Credentials

First, use the DBMS_CLOUD package to create credentials for accessing cloud resources.

BEGIN    DBMS_CLOUD.CREATE_CREDENTIAL(        credential_name => ‘your_credential_name’,        username => ‘your_username’,        password => ‘your_password’    ); END;

Step 2: Create the Database Link with DBMS_CLOUD_ADMIN Package

Next, create the database link using the DBMS_CLOUD_ADMIN package, which allows Oracle Autonomous Database (ADB) to connect to an external database, such as an on-premises Oracle Database.

BEGIN    DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(        db_link_name      => ‘my_onprem_db_link’,                 hostname          => ‘enter_Host_name’,                         port              => 1521,                                        service_name      => ‘my_service’,                        ssl_server_cert_dn => NULL,                          credential_name   => ‘my_credential’,                  directory_name    => NULL,                              private_target    => TRUE                           ); END;

Step 3: Test the Connection to the Remote Database

Finally, to test the connection to the remote database, use the following query:

SELECT * FROM dual@my_remote_db_link;

 Note: Replace my_remote_db_link with the actual DB link name you want to connect to.