Sharing Data from On-Premise Oracle Databases

January 12, 2024 | 4 minute read
Alexey Filanovskiy
Product Manager
Text Size 100%:

Data sharing has been a key feature in Autonomous databases, garnering significant interest. However, the challenge of sharing data from on-premises Oracle databases remained unresolved. In this post, we’ll explore a solution enabling customers to share their on-prem Oracle data via Delta Sharing protocols.

It’s important to note that while our focus is on Oracle databases, the data source for this sharing approach isn’t limited to Oracle. It’s applicable to any data source compatible with Data Transforms, such as MySQL among others. Given the high demand for solutions in sharing Oracle database data on-premise, this blog will concentrate on this specific use case

Understanding the Architecture for On-Premise Data Sharing

To facilitate the sharing of on-premise data, users require an Oracle Cloud Infrastructure (OCI) account and an Autonomous Database (ADB). Within ADB, the “Data Transforms” feature plays a pivotal role. This tool is designed for ELT (Extract, Load, Transform) operations.

The process begins with “Data Transforms” connecting to the on-premise Oracle Database, facilitating the transfer of data into ADB. Once the data is securely loaded, the data-sharing pipeline is activated. Upon completion of this pipeline, the data is made accessible via the Delta Sharing protocol. This process can be set to recur periodically, ensuring continuous data sharing.

High-level diagram to share Oracle database on-prem data

Comprehensive Guide: Opening On-Prem Oracle Data to the Public via Delta Sharing

In this section, I’ll provide a detailed, step-by-step walkthrough on how to make on-premise Oracle data publicly accessible using the Delta Sharing protocol.

  • Start by navigating to the Autonomous Database Data Studio. Select the ‘Data Transformation’ option.
Data Transforms tile
  • In the Data Transforms Interface select the ‘Data Load’ option. Specify your source and target connections:

Source Connection: Your on-premise Oracle database.

Target Connection: The Autonomous Database (ADB).

  • In the Data Load Configuration. Select the table(s) you wish to share and choose an ‘Incremental Column’ and a ‘Merge Key’.

Incremental Column: This is used to identify newly added or updated data within your tables.

Merge Key: This key uniquely matches records, facilitating updates or merges.

  • Establish Your Workflow. Create a workflow configuration and set it to trigger the data-sharing API once the data loading is complete.

This step is crucial to automate the process, ensuring that your data sharing initiates immediately and efficiently after the data load

  • Within your workflow settings, specify the SQL command. Ensure this command is set to execute only after the data loading process has successfully completed. By configuring the workflow in this manner, you ensure that the SQL command is executed precisely at the right moment, maintaining the integrity and efficiency of your data-sharing process
  • Finalizing the Workflow: Setting Up the PLSQL Publication. Define the PL/SQL procedure within the workflow. This PL/SQL should be configured to publish the data once the loading process is complete. To learn more about Data Sharing PLSQL API check this Live Lab.
  • The final step is to create a schedule for your workflow. This schedule dictates when the workflow, including the data loading and publishing processes, will be executed. Setting up a regular schedule ensures your data-sharing process is automated and occurs at consistent, predetermined intervals.

Congratulations! You’ve successfully set up your workflow. Now, sit back and enjoy the benefits of seamlessly shared data.

Conclusion

We’ve journeyed through the process of opening up on-premise Oracle data to the public using the Delta Sharing protocol. From setting up your Autonomous Database Data Studio to creating a workflow and scheduling it, each step brings us closer to a more connected and data-empowered world.

This blog not only highlights the technical capabilities of Oracle databases and Delta Sharing but also underscores the importance of data accessibility and collaboration in today’s digital landscape. As we continue to navigate and innovate in the realm of data sharing, the possibilities for growth and insight seem limitless.

Alexey Filanovskiy

Product Manager

My role involves overseeing the product management of Data Lake features for the Oracle Autonomous Database.


Previous Post

A quick recap of every new feature released during 2023

Keith Laker | 8 min read

Next Post


New Data Loading experience in Autonomous Database Data Studio

Alexey Filanovskiy | 3 min read
Everything you need to know about data warehousing with the world's leading cloud solution provider