DBMS_CLOUD in Oracle Autonomous Database

DBMS_CLOUD in Oracle Autonomous Database: A Complete Guide

Introduction

Oracle Autonomous Database (ADB) is a powerful, self-driving database that automates patching, scaling, and performance tuning. A key component of ADB is the DBMS_CLOUD package, which simplifies cloud data integration, external table management, and credential security.

In this comprehensive guide, we will explore DBMS_CLOUD capabilities, practical use cases, and real-world examples to help you optimize your Autonomous Database workflows.

What is DBMS_CLOUD?

DBMS_CLOUD is an Oracle PL/SQL package that enables seamless interaction with cloud-based storage services such as:

• Oracle Cloud Infrastructure (OCI) Object Storage
• Amazon S3
• Microsoft Azure Blob Storage
• Google Cloud Storage

Key Features of DBMS_CLOUD

1. Load Data Efficiently: Import CSV, JSON, and Parquet files from cloud storage to your database.
2. Manage Cloud Credentials: Securely store and use credentials for external data sources.
3. Create External Tables: Query cloud-stored data without importing it.
4. List & Manage Cloud Files: Access file details, delete files, and retrieve metadata.
5. Automate Data Pipelines: Integrate with Oracle GoldenGate, ETL tools, and Data Lake solutions.

Setting Up DBMS_CLOUD in Autonomous Database

Before using DBMS_CLOUD, ensure your ADB instance is running, and you have the necessary IAM permissions to access cloud storage.

Step 1: Create a Cloud Credential

A credential is required to access external cloud storage securely.

BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'my_oci_credential',
username => 'my_user',
password => 'my_auth_token'
);
END;

🔹 For OCI Object Storage, use an Auth Token instead of a password.

Step 2: Verify Your Credential

SELECT * FROM USER_CREDENTIALS;

If successful, the credential should appear in the results.

Use Case 1:

Loading Data from Cloud Storage

To load structured data from OCI Object Storage, use the COPY_DATA procedure.

BEGIN
DBMS_CLOUD.COPY_DATA(
table_name => 'SALES_DATA',
credential_name => 'my_oci_credential',
file_uri_list => 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/my_namespace/b/my_bucket/o/sales.csv',
format => '{"delimiter": ",", "skipheaders": 1}'
);
END;

✅ Best for: Bulk-loading transactional data, financial records, or IoT sensor logs.

🔹 Pro Tip: Automate this process by scheduling the command via DBMS_SCHEDULER.

Use Case 2:

Creating an External Table for Cloud Data Analysis

Instead of loading data, you can query cloud-stored files directly using external tables.

BEGIN
DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
table_name => 'EXT_SALES_DATA',
credential_name => 'my_oci_credential',
file_uri_list => 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/my_namespace/b/my_bucket/o/sales.csv',
format => '{"delimiter": ",", "skipheaders": 1}'
);
END;

🔹 When to use? Useful for analyzing large datasets without ingesting them into ADB.

SELECT * FROM EXT_SALES_DATA WHERE REGION = 'US';

✅ Best for: Big data analytics, machine learning models, and ETL pipelines.

Use Case 3:

Managing Cloud Storage Files

To list all files in a specific cloud storage bucket:

SELECT * FROM TABLE(DBMS_CLOUD.LIST_FILES('my_oci_credential', 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/my_namespace/b/my_bucket/'));

To delete a file from Object Storage:

BEGIN
DBMS_CLOUD.DELETE_FILE(
credential_name => 'my_oci_credential',
file_uri => 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/my_namespace/b/my_bucket/o/old_data.csv'
);
END;

✅ Best for: Data lifecycle management and automated cleanups.

Use Case 4:

Diagnosing Data Load Errors

If a COPY_DATA operation fails, check for errors using:

SELECT * FROM USER_LOAD_OPERATIONS WHERE TYPE = 'COPY';

For detailed error logs:

SELECT * FROM USER_LOAD_OPERATIONS WHERE STATUS != 'COMPLETED';

✅ Best for: Debugging file format mismatches and missing credentials.

Best Practices for DBMS_CLOUD

✅ Optimize Performance: Use parallel execution and batch loading for large datasets.

✅ Enhance Security: Store credentials securely and limit access using IAM policies.

✅ Automate Pipelines: Schedule COPY_DATA operations to refresh data in real-time. ✅ Monitor Errors: Regularly check USER_LOAD_OPERATIONS for failed loads.

Final Thoughts

The DBMS_CLOUD package revolutionizes how Oracle Autonomous Database interacts with cloud storage. Whether you are loading massive datasets, analyzing cloud-stored data, or automating ETL workflows, DBMS_CLOUD provides a secure and efficient solution.

📌 Next Steps:

• Try executing a COPY_DATA command on your ADB instance.
• Create an external table and run SQL queries on cloud files.
• Set up automated data ingestion for real-time analytics.

By leveraging DBMS_CLOUD, you can unlock seamless data integration and make your Autonomous Database truly autonomous! 🚀

Useful Resources –

How To Setup And Use DBMS_CLOUD Package (Doc ID 2748362.1)

FAQs

❓ Can DBMS_CLOUD connect to non-OCI storage like AWS S3?
✔️ Yes! It supports Amazon S3, Azure Blob, and Google Cloud Storage.

❓ Is DBMS_CLOUD available in all Oracle Cloud regions?
✔️ Yes, but ensure your IAM policies allow cloud storage access.

❓ How do I schedule a data load job?
✔️ Use DBMS_SCHEDULER.CREATE_JOB to automate data imports.

Got questions? Drop them in the comments below! 🚀

Author – Rohit Mishri