Create Database Links to Non-Oracle Databases with Oracle-Managed Heterogeneous Connectivity
Autonomous Database support for Oracle-managed heterogeneous connectivity makes it easy to create database links to non-Oracle databases. When you use database links with Oracle-managed heterogeneous connectivity, Autonomous Database configures and sets up the connection to the non-Oracle database.
The following are prerequisites to use Oracle-managed heterogeneous connectivity with Autonomous Database:
-
The target database must be accessible from the public internet on the port number supported for the specified database type. See Oracle-Managed Heterogeneous Connectivity Database Types and Ports for the list of supported non-Oracle database types and ports.
-
The target database must be configured to allow incoming SSL/TLS connections.
Note:
Oracle-managed heterogeneous connectivity supports connections to Oracle MySQL Database Service on private endpoints. When you connect to MySQL on a private endpoint, the connections uses TCP protocol and it does not require SSL/TLS to be configured on the target database. See Create Database Links to Oracle MySQL on a Private Endpoint with Oracle-Managed Heterogeneous Connectivity for more information.To create database links to a non-Oracle database using Oracle-managed heterogeneous connectivity, do the following:
Note:
For the credentials you create in Step 1, the target database credentials, if the password of the target user changes you can update the credential that contains the target user's credentials as follows:
BEGIN
DBMS_CLOUD.UPDATE_CREDENTIAL
(
credential_name => 'AWS_REDSHIFT_LINK_CRED',
attribute => 'PASSWORD',
value => 'password');
END;
/
Where password is the new password.
After this operation, the existing database links that use this credential continue to work without having to drop and recreate the database links.
For additional information, see:
-
Supported SQL Syntax and Functions in Oracle Database Gateway for ODBC User's Guide
- Create Database Links to Oracle MySQL on a Private Endpoint with Oracle-Managed Heterogeneous Connectivity
Autonomous Database support for Oracle-managed heterogeneous connectivity makes it easy to create database links to Oracle MySQL Database Service on a private endpoint. When you use database links with Oracle-managed heterogeneous connectivity, Autonomous Database configures and sets up the connection to the Oracle MySQL Database Service. - Oracle-Managed Heterogeneous Connectivity Database Types and Ports
Shows the non-Oracle databases that you can connect to from Autonomous Database with Oracle-managed heterogeneous connectivity, and lists the supported port value for each database type. Also provides a link where you can see the supported database versions for each database type. - Access Heterogeneous Connectivity Information and Samples
Oracle Autonomous Database provides heterogeneous connectivity information for database links to non-Oracle databases.
Parent topic: Create Database Links to Non-Oracle Databases
Create Database Links to Oracle MySQL on a Private Endpoint with Oracle-Managed Heterogeneous Connectivity
Autonomous Database support for Oracle-managed heterogeneous connectivity makes it easy to create database links to Oracle MySQL Database Service on a private endpoint. When you use database links with Oracle-managed heterogeneous connectivity, Autonomous Database configures and sets up the connection to the Oracle MySQL Database Service.
Note:
Database links from an Autonomous Database to an Oracle MySQL Database Service that is on a private endpoint are only supported in commercial regions and US Government regions.This feature is enabled by default in all commercial regions.
This feature is enabled by default in US Government regions for newly provisioned databases.
For existing US Government databases on a private endpoint, if you want to create database links from an Autonomous Database to a target in a US Government region, please file a Service Request at Oracle Cloud Support and request to enable the private endpoint in government regions database linking feature.
US Government regions include the following:
The following are prerequisites to use Oracle-managed heterogeneous connectivity with Oracle MySQL Database Service on a private endpoint:
- Create a DNS name using private DNS Zone pointing to private IP of your Oracle MySQL Database Service in your VCN. See Private DNS.
- Create an Autonomous Database with a Private Endpoint on same subnet.
-
Configure the VCN for the Oracle MySQL Database Service to allow incoming connections on port 3306.
To create database links to a Oracle MySQL Database Service on a private endpoint using Oracle-managed heterogeneous connectivity, do the following:
Note:
For the credentials you create in Step 1, the target database credentials, if the password of the target user changes you can update the credential that contains the target user's credentials as follows:
BEGIN
DBMS_CLOUD.UPDATE_CREDENTIAL
(
credential_name => 'MYSQL_LINK_CRED',
attribute => 'PASSWORD',
value => 'password');
END;
/
Where password is the new password.
After this operation, the existing database links that use this credential continue to work without having to drop and recreate the database links.
For additional information, see:
-
Supported SQL Syntax and Functions in Oracle Database Gateway for ODBC User's Guide
Oracle-Managed Heterogeneous Connectivity Database Types and Ports
Shows the non-Oracle databases that you can connect to from Autonomous Database with Oracle-managed heterogeneous connectivity, and lists the supported port value for each database type. Also provides a link where you can see the supported database versions for each database type.
Note:
Oracle uses Progress DataDirect connectors. The Database Support column provides links to the Progress website where you can find the supported database versions for each database type.Database Type | db_type Value | Required Port | Database Support |
---|---|---|---|
Amazon Redshift |
|
5439 |
Amazon Redshift Supported Versions |
Azure SQL Microsoft SQL Server Azure Synapse Analytics |
|
1433 |
|
Apache Hive |
HIVE |
443 |
Hive Supported Versions |
Google Analytics |
|
443 |
|
Google BigQuery |
|
443 |
|
IBM Db2 11.5.6 or greater IBM Db2 11.5.5 or less |
DB2 |
25000 50000 |
IBM Db2 Supported Versions |
MongoDB |
MONGODB |
27017 |
|
MySQL |
|
3306 |
MySQL Supported Versions |
MySQL Community Edition |
|
3306 |
|
PostgreSQL |
POSTGRES |
5432 |
PostgreSQL Supported Versions |
Salesforce |
|
19937 |
Salesforce Supported Versions |
ServiceNow |
|
443 |
ServiceNow Supported Versions |
Snowflake |
|
443 |
Snowflake Supported Versions |
Access Heterogeneous Connectivity Information and Samples
Oracle Autonomous Database provides heterogeneous connectivity information for database links to non-Oracle databases.
The HETEROGENEOUS_CONNECTIVITY_INFO
view provides
information on supported Oracle Heterogeneous Connectivity types and shows a
PL/SQL code sample for each supported type.
For example:
SELECT * FROM HETEROGENEOUS_CONNECTIVITY_INFO WHERE DATABASE_TYPE = 'HIVE';
- HETEROGENEOUS_CONNECTIVITY_INFO View
TheHETEROGENEOUS_CONNECTIVITY_INFO
view lists connectivity information and examples for connecting with PL/SQL using database links and Oracle Managed Heterogeneous Connectivity.
HETEROGENEOUS_CONNECTIVITY_INFO View
The HETEROGENEOUS_CONNECTIVITY_INFO
view lists connectivity information and examples for connecting with PL/SQL using database
links and Oracle Managed Heterogeneous Connectivity.
Column | Datatype | Description |
---|---|---|
DATABASE_TYPE |
VARCHAR2(32) |
Database type value used with
|
REQUIRED_PORT |
NUMBER |
Supported port values for the database type. |
DESCRIPTION |
CLOB |
Specifies a description for the
|
OPTIONAL_PARAMETERS |
VARCHAR2(1024) |
Specifies the valid optional parameters for the
|
SAMPLE_USAGE |
CLOB |
Shows sample PL/SQL usage for the |