Skip to Main Content
  • Questions
  • export DB dumps using DBMS_DATAPUMP api to the Object storage on Oracle Cloud Infrastructure

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Amar.

Asked: November 18, 2020 - 12:33 pm UTC

Last updated: January 25, 2021 - 2:49 pm UTC

Version: 12.1.0.2.0

Viewed 1000+ times

You Asked

Hi,
I have a requirement where I need to export DB dumps using DBMS_DATAPUMP api to the Object storage on Oracle Cloud Infrastructure.

I found below statement on official document to export dumps at object storage using expdp utility.

expdp admin/password@ADWC1_high \
     filesize=5GB \
     dumpfile=default_credential:https://objectstorage.us-ashburn-1.oraclecloud.com/n/namespace-string/b/bucketname/o/exp%U.dmp \
     parallel=16 \
     encryption_pwd_prompt=yes \
     logfile=export.log \
     directory=data_pump_dir


But I didn’t get any reference for DBMS_DATAPUMP api, Could you please help me to know how to perform same task using DBMS_DATAPUMP api.

and Chris said...

Sorry, I'm not able to find any official documentation on this either! I'm following up internally.

If you're using ATP, you can use dbms_cloud.export_data to copy data to the object store as shown in this guide:

https://docs.oracle.com/en/cloud/paas/atp-cloud/atpug/export-data-select.html#GUID-7DAB00F6-65B8-4678-B222-7939001A3469

Though note the files generated by this are incompatible with impdp - you need to use dbms_cloud to import them or external tables using the oracle_datapump driver.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Export dumps directly on Object store.

A reader, January 13, 2021 - 6:17 am UTC

We are still looking for the feature that will export dump files directly to the Object store using DBMS_DATAPUMP api. Could you please let me know if you have any update on this.
Connor McDonald
January 14, 2021 - 5:29 am UTC

Documented in 21c using the constant DBMS_DATAPUMP.KU$_FILE_TYPE_URIDUMP_FILE
and since the constant exists in 19c I'd suspect it works there too

https://docs.oracle.com/en/database/oracle/oracle-database/21/sutil/using-oracle-data-pump-api.html#GUID-2CE28CE2-4B92-4473-8A00-71E23CFEEE3C


A reader, January 19, 2021 - 1:45 pm UTC

Thanks for the reply!
Actually I tried the example that will export dump file directly to the Object store but getting below error and not sure why am I getting this even after correct URI for bucket and Credential.
Error:
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file
"https://swiftobjectstorage.ap-hyderabad-1.oraclecloud.com/v1/axqj0jxv2wlw/LetHaveBucket/o/exp_datapump_api01.DMP"
ORA-17503: ksfdopn:11 Failed to open file
https://swiftobjectstorage.ap-hyderabad-1.oraclecloud.com/v1/axqj0jxv2wlw/LetHaveBucket/o/exp_datapump_api01.DMP
ORA-17500: ODM err:ODM Credential Not Found
ORA-17503: ksfdopn:11 Failed to open file
https://swiftobjectstorage.ap-hyderabad-1.oraclecloud.com/v1/axqj0jxv2wlw/LetHaveBucket/o/exp_datapump_api01.DMP
ORA-17500: ODM err:ODM Credential Not Found


Actually we are working on FA SaaS application that uses ATP database 19c(19.5). Could you please confirm which DB version supports feature of exporting dumps directly to the object store using DBMS_DATAPUMP api.
Chris Saxon
January 25, 2021 - 2:49 pm UTC

Sorry I'm not sure what the issue is here; try following up with support.

More to Explore

Data Pump

All of the database utilities including Data Pump are explained in the Utilities guide.