How to Create DB Link for Oracle ADB? - ABOTTS INC

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.