Forum Stats

  • 3,839,722 Users
  • 2,262,530 Discussions
  • 7,901,048 Comments

Discussions

Data Pump Export to Object Storage via SQL Developer

User_FBQT3
User_FBQT3 Member Posts: 3 Red Ribbon

Hi,

I am trying to run data pump in Autonomous Database via SQL Developer Data Pump Export Wizard. Target is to send the dump to the Object Storage. We won't have access to the server so SQL Developer is the only option.

Data pump is completing but unable to copy to OSS and I get below exception:


######################

Exception occurred during previous Data Pump Export wizard


Stage: Data Pump Export 

Title: No files found 

MessageText:

No files when expanding dump names

#####################


Am I using the correct parameters here??

Target URL (Bucket): https://objectstorage.us-ashburn-1.oraclecloud.com/n/xxxxxx/b/oci-bucket001/o/

Oracle Directory path Override: https://objectstorage.us-ashburn-1.oraclecloud.com/n/xxxxxx/b/oci-bucket001/o/test_export%U.dmp

Answers

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,705 Employee

     We won't have access to the server so SQL Developer is the only option


    Copying the DMP file(s) to the Object Store with SQLDev will only work if SQLDev is on the server with the database (and has access to those DMP files).

    We know that's not ideal, so we're adding Data Pump support to SQLcl and it will be able to stream the DMPs files down from the server (from anywhere) and then load them up to the Object Store. That's coming later this year.

  • User_FBQT3
    User_FBQT3 Member Posts: 3 Red Ribbon

    Copying the DMP file(s) to the Object Store with SQLDev will only work if SQLDev is on the server with the database (and has access to those DMP files). -- How should that be set up in Autonomous DB?

    Currently, I am accessing ADW through the SQL Developer in my RDP. And since I don't have access to the Linux server, we cannot issue expdp commands, so we are trying to use SQL Dev to run the export.

    The goal is to do table refresh to our lower-env, but ADB only offer full database clone.


    Thanks,

    -Jake

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,705 Employee

    We built the feature you're using to help get their data INTO Autonomous/Object store...you're already there, so it's much easier.


    You can do everything via HTTPS if you want. You can kick off the export job via:

    + REST API or

    + SQLDev Wizard or

    + PL/SQL calls in worksheet...


    ...and then download the DMP file and logs from SQL Developer Web. Or use DBMS_CLOUD to PUT the file to Object Store

    https://docs.oracle.com/en/cloud/paas/autonomous-database/adbsa/export-data-object-store.html#GUID-7FCB19A7-684F-467A-84B9-D503D9AA7328