Data Pump Import from OCI Storage to Autonomous Database

This is the third of three blogs about using Data Pump on an Autonomous Oracle database.  

1. General considerations and setup.
2. Export
3. Import

This blog post demonstrates importing into an Autonomous database directly from OCI Bucket Storage. Import is very similar to export, discussed in the previous post.  This blog is mostly for completeness, but I will illustrate a couple of variations.

Import a Whole Schema from a Directory of Data Pump Export Files

As in the previous example, this anonymous PL/SQL block creates a data pump import job.  Several elements must be set up.

• I am using the export from an autonomous database that I created in the previous blog.  I have a directory containing several 24Mb dump files, and a zero-length dump file in the parent directory.  

SELECT object_name, bytes/1024/1024 Mb
FROM table(
  DBMS_CLOUD.LIST_objects
  (credential_name => 'OBJECT_STORE_CRED',
  location_uri => 'https://objectstorage.uk-london-1.oraclecloud.com/n/lrp1qmpxv8ea/b/bucket-gofaster1/o/'
  ));

OBJECT_NAME                                                    MB
---------------------------------------------------------- ------
…
export_strava_20260514.dmp                                    .00
export_strava_20260514.dmp_segments/aaaaaa                   2.00
export_strava_20260514.dmp_segments/aaaaab                  24.00
…
export_strava_20260514.dmp_segments/aaaabe                  24.00
export_strava_20260514.dmp_segments/aaaabf                  12.46
export_strava_20260514.log                                    .00

• Note that I have to tell Data Pump to import the zero-length dump file export_strava_20260514.dmp, in the root direct, and it will automatically load the files from the subdirectory.
• The data pump job is opened in SCHEMA mode because I am going to import a whole schema from a directory of data pump export files produced by an export from an autonomous database.  The STRAVA schema already exists and has the privilege to create all the objects (otherwise, I get even more errors during the import).
• The table exists action is TRUNCATE.  If the table already exists, it will be truncated before new data is imported.

DECLARE
  h NUMBER;
  l_file_name VARCHAR2(100) := 'export_strava_20260514.dmp';
  l_urifile_name VARCHAR2(200);
  l_dir VARCHAR2(100) := 'DATA_PUMP_DIR';
BEGIN
  l_urifile_name := 'https://objectstorage.uk-london-1.oraclecloud.com/n/lrp********ea/b/bucket-gofaster1/o/'
                    ||l_file_name;
  h := DBMS_DATAPUMP.OPEN
      (operation => 'IMPORT'
      ,job_mode  => 'SCHEMA'
  );

  DBMS_DATAPUMP.ADD_FILE
    (handle    => h
    ,filename  => l_urifile_name
    ,filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_URIDUMP_FILE
    ,directory => 'OBJECT_STORE_CRED'
  );

  DBMS_DATAPUMP.ADD_FILE
      (handle    => h
      ,filename  => 'import_strava.log'
      ,directory => l_dir
      ,filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE
      ,reusefile => 1
  );
 
  DBMS_DATAPUMP.METADATA_FILTER
      (handle => h
      ,name   => 'SCHEMA_EXPR'
      ,value  => 'IN (''STRAVA'')'
  );
   
  dbms_datapump.set_parameter
      (handle => h
      ,name   => 'TABLE_EXISTS_ACTION'
      ,value  => 'TRUNCATE'
  );

  DBMS_DATAPUMP.START_JOB(h);
  DBMS_DATAPUMP.DETACH(h);
END;
/

I can see the job by querying DBA_DATAPUMP_JOBS.

OWNER_NAME JOB_NAME             OPERATION  JOB_MODE   STATE            DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
---------- -------------------- ---------- ---------- ------------ ---------- ----------------- -----------------
ADMIN      SYS_IMPORT_SCHEMA_01 IMPORT     SCHEMA     EXECUTING             1                 0                 2

The import log shows that all the data has been loaded.  I have a few error messages.  Some I can ignore, some I need to address.

Master table "ADMIN"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "ADMIN"."SYS_IMPORT_SCHEMA_01":  
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"STRAVA" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ORACLE_OBJECT_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PASSWORD_HISTORY
ORA-39111: Dependent object type PASSWORD_HISTORY:"STRAVA" skipped, base object type USER:"STRAVA" already exists
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA/LOGREP
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
…
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "STRAVA"."ACTIVITIES_EXT"                       0 KB       0 rows
. . imported "STRAVA"."SCHEDULER$_JOB_ARG"                 5.4 KB       4 rows
. . imported "STRAVA"."MY_AREAS"                         384.5 MB  117584 rows
. . imported "STRAVA"."MY_AREA_CODES"                      5.8 KB      58 rows
. . imported "STRAVA"."WEBHOOK_EVENTS"                    10.1 KB      34 rows
. . imported "STRAVA"."ACTIVITIES"                         348 MB    5616 rows
. . imported "STRAVA"."STRAVA_TOKENS"                      5.2 KB       1 rows
. . imported "STRAVA"."API_LOG"                            5.8 KB       8 rows
. . imported "STRAVA"."ACTIVITY_AREAS"                     1.2 MB  134828 rows
. . imported "STRAVA"."GEAR"                               6.4 KB      12 rows
Processing object type SCHEMA_EXPORT/TABLE/IDENTITY_COLUMN
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/DOMAIN_INDEX/INDEX
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ/SCHEDULER
ORA-39083: Object type SCHEDULER:PROCOBJ failed to create with error:
ORA-27476: "SYS"."PURGE_API_LOG_CLASS" does not exist
…
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA/SCHEDULER
ORA-39083: Object type SCHEDULER:PROCACT_SCHEMA failed to create with error:
ORA-27476: "STRAVA"."BATCH_LOAD_ACTIVITIES_JOB" does not exist
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA/LBAC_EXP
ORA-39082: Object type TYPE:"STRAVA"."metadataType178_T" created with compilation warnings
…
ORA-39082: Object type VIEW:"STRAVA"."GEAR2_DV" created with compilation warnings
ORA-39082: Object type PACKAGE BODY:"STRAVA"."STRAVA_HTTP" created with compilation warnings
Job "ADMIN"."SYS_IMPORT_SCHEMA_01" completed with 304 error(s) at Fri May 15 21:12:52 2026 elapsed 0 00:17:30

PL/SQL procedure successfully completed.

Import Single Table from Single File Data Pump Export

In this example, I am importing just a single table from a single data pump export file that was created by exporting to a database directory.  

• Open the data pump job in TABLE mode.   
• Metadata filter to the schema called STRAVA
• Metadata filter to the object called ACTIVITY_AREAS.  This filter applies to table names because the data pump job was opened in TABLE mode.
• Set the table exists action to TRUNCATE.

DECLARE
  h NUMBER;
  l_file_name VARCHAR2(100) := 'export_strava_20260428_01.dmp';
  l_urifile_name VARCHAR2(200);
  l_dir VARCHAR2(100) := 'DATA_PUMP_DIR';
BEGIN
  l_urifile_name := 'https://objectstorage.uk-london-1.oraclecloud.com/n/lrp********ea/b/bucket-gofaster1/o/'
                    ||l_file_name;
  h := DBMS_DATAPUMP.OPEN
  (operation => 'IMPORT'
  ,job_mode  => 'TABLE'
  );

  DBMS_DATAPUMP.ADD_FILE
    (handle    => h
    ,filename  => l_urifile_name
    ,filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_URIDUMP_FILE
    ,directory => 'OBJECT_STORE_CRED'
    --,credential_name => 'OBJECT_STORE_CRED'
  );

  DBMS_DATAPUMP.ADD_FILE
  (handle    => h
  ,filename  => 'import_strava.log'
  ,directory => l_dir
  ,filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE
  ,reusefile => 1
  );
 
  DBMS_DATAPUMP.METADATA_FILTER
  (handle => h
  ,name   => 'SCHEMA_EXPR'
  ,value  => 'IN (''STRAVA'')'
  );
   
  DBMS_DATAPUMP.METADATA_FILTER
  (handle => h
  ,name   => 'NAME_EXPR'
  ,value  => 'IN (''ACTIVITY_AREAS'')'
  );
  
  dbms_datapump.set_parameter
  (handle => h
  ,name   => 'TABLE_EXISTS_ACTION'
  ,value  => 'TRUNCATE'
  );

  DBMS_DATAPUMP.START_JOB(h);
  DBMS_DATAPUMP.DETACH(h);
END;
/

The log shows that just a single table was imported, and any preexisting data was truncated.

Master table "ADMIN"."SYS_IMPORT_TABLE_03" successfully loaded/unloaded
Starting "ADMIN"."SYS_IMPORT_TABLE_03":  
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Table "STRAVA"."ACTIVITY_AREAS" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "STRAVA"."ACTIVITY_AREAS"                     1.2 MB  134372 rows
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "ADMIN"."SYS_IMPORT_TABLE_03" successfully completed at Wed May 13 20:53:29 2026 elapsed 0 00:00:21

Data Pump Export to OCI Storage from Autonomous Database

This is the second of three blogs about using Data Pump on an Autonomous Oracle database.  

1. General considerations and setup.
2. Export
3. Import

I have found places on the internet that say you cannot export directly from Autonomous Database to OCI Storage, and I struggled to find an example that worked.  However, the following example does work!

Export Job

This anonymous PL/SQL block creates a data pump export job.  Several elements must be set up.

• A new data pump job needs to be created/opened. In this example, I am exporting the schema STRAVA.
• Some data pump parameters are set.
• The data pump export file location and name are set.  
• I want to write the dump file directly to the OCI bucket storage, so I must specify the file type as DBMS_DATAPUMP.KU$_FILE_TYPE_URIDUMP_FILE.  On an autonomous database, this will be the prefix of the directory name that will contain the various pieces of the export.
• DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE  can only be used to write to a database directory. 
• The data pump log file destination and name are specified.  Again, DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE can only specify a database directory.  Therefore, it will be the DATA_PUMP_DIR database directory.
• A metadata filter restricts the export to just objects in the STRAVA schema.

DECLARE
  h NUMBER;
  l_file_name VARCHAR2(100);
  l_urifile_name VARCHAR2(200);
  l_dir VARCHAR2(100) := 'DATA_PUMP_DIR';
BEGIN
  l_file_name := 'export_strava_%T';
  l_urifile_name := 'https://objectstorage.uk-london-1.oraclecloud.com/n/lr********ea/b/bucket-gofaster1/o/'||l_file_name;
  h := DBMS_DATAPUMP.OPEN
       (operation => 'EXPORT'
       ,job_mode => 'SCHEMA'
       );

  dbms_datapump.set_parameter
       (handle => h
       ,name   => 'COMPRESSION'
       ,value  => 'ALL'
       );

  dbms_datapump.set_parameter
       (handle => h
       ,name   => 'ESTIMATE'
       ,value  => 'BLOCKS'
       );

  DBMS_DATAPUMP.ADD_FILE
       (handle    => h
       ,filename  => l_urifile_name||'.dmp'
       ,directory => 'OBJECT_STORE_CRED'
       ,filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_URIDUMP_FILE
       ,reusefile => 1
       );

  DBMS_DATAPUMP.ADD_FILE
       (handle    => h
       ,filename  => l_file_name||'.log'
       ,directory => l_dir
       ,filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE
       ,reusefile => 1
  );

  DBMS_DATAPUMP.METADATA_FILTER(h, 'SCHEMA_EXPR', 'IN (''STRAVA'')');
  DBMS_DATAPUMP.START_JOB(h);  
END;
/

Things That Did Not Work

I have not been able to write the data pump log file to the OCI bucket storage.  That can only be written to a database directory.

  DBMS_DATAPUMP.ADD_FILE
       (handle    => h
       ,filename  => l_urifile_name||'.log'
       ,directory => 'OBJECT_STORE_CRED'
       ,filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE
       ,reusefile => 1
  );

Normally, I can specify a maximum file size for a data pump export file.  However, on Autonomous, the maximum file size setting was ignored.  I always get 24Mb chunks

  DBMS_DATAPUMP.ADD_FILE
       (handle    => h
       ,filename  => l_urifile_name||'.dmp'
       ,directory => 'OBJECT_STORE_CRED'
       ,filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_URIDUMP_FILE
       ,filesize  => '4G' --but this is ignored
       ,reusefile => 1
       );

How to See What Data Pump Jobs are Running?

The view DBA_DATAPUMP_JOBS reports on all data pump jobs, irrespective of their state.

select * from dba_datapump_jobs
/

OWNER_NAME JOB_NAME             OPERATION  JOB_MODE   STATE            DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
---------- -------------------- ---------- ---------- ------------ ---------- ----------------- -----------------
ADMIN     SYS_EXPORT_SCHEMA_01 EXPORT    SCHEMA    EXECUTING            1                 1                 3

DBA_DATAPUMP_SESSIONS reports on the database sessions currently attached to a data pump job.

select p.owner_name, p.job_name, p.inst_id, p.saddr, session_type, sid
from dba_datapump_sessions p
  left outer join gv$session s on s.inst_id = p.inst_id and s.saddr = p.saddr
;

OWNER_NAME JOB_NAME                INST_ID SADDR            SESSION_TYPE          SID
---------- -------------------- ---------- ---------------- -------------- ----------
ADMIN     SYS_EXPORT_SCHEMA_01          3 000000128EF28558 DBMS_DATAPUMP      22536
ADMIN     SYS_EXPORT_SCHEMA_01          3 0000001248491BF0 WORKER             31689
ADMIN     SYS_EXPORT_SCHEMA_01          3 00000012976F07F8 MASTER             33092

Wait for Running Data Pump Jobs

This PL/SQL block uses DBMS_DATAPUMP.WAIT_FOR_JOB to wait for a currently executing data pump job to complete.

DECLARE
  h NUMBER;
  l_job_name VARCHAR2(100);
  l_job_state VARCHAR2(100);
  e_job_does_not_exists EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_job_does_not_exists,-31626s);
BEGIN
  FOR i IN (SELECT DISTINCT owner_name, job_name FROM dba_datapump_jobs) 
  LOOP
    BEGIN
      l_job_name := i.owner_name||'.'||i.job_name;
      -- Attach to running job by name
      h := DBMS_DATAPUMP.ATTACH(job_name => l_job_name);

      DBMS_DATAPUMP.WAIT_FOR_JOB(handle => h, job_state => l_job_state);
	  dbms_output.put_line('Datapump Job: '||l_job_name||', Status: '||l_job_state);
    
      DBMS_DATAPUMP.DETACH(h);
	EXCEPTION 
	  WHEN e_job_does_not_exists THEN 
	    dbms_output.put_line('Datapump Job: '||l_job_name||' does not exist');
	END;
  END LOOP;
END;
/

Spool Log File

I have not been able to write data pump log files directly to OCI storage, only to a database directory.  However, they are generally small.  I can read them and print them out with DBMS_OUTPUT.

SET SERVEROUTPUT ON SIZE UNLIMITED echo on;
DECLARE
    l_file   UTL_FILE.FILE_TYPE;
    l_line   VARCHAR2(32767);
    l_dir    CONSTANT VARCHAR2(30) := 'DATA_PUMP_DIR';
BEGIN
  FOR i IN (
    SELECT * FROM TABLE(DBMS_CLOUD.LIST_FILES('DATA_PUMP_DIR'))
	WHERE object_name like 'export_strava%.log'
  ) LOOP
    BEGIN
      -- Open the file for reading
      l_file := UTL_FILE.FOPEN(location => l_dir,
                               filename => i.object_name,
                               open_mode => 'R',
                               max_linesize => 32767);

      -- Loop through each line
      LOOP
        BEGIN
          UTL_FILE.GET_LINE(l_file, l_line);
          DBMS_OUTPUT.PUT_LINE(l_line);
        EXCEPTION
          WHEN NO_DATA_FOUND THEN
            EXIT;  -- exit loop at end of file
        END;
      END LOOP;
      -- Close the file
      UTL_FILE.FCLOSE(l_file);
    EXCEPTION
      WHEN UTL_FILE.INVALID_PATH THEN
        DBMS_OUTPUT.PUT_LINE('Invalid directory path: ' || l_dir);
      WHEN UTL_FILE.INVALID_MODE THEN
        DBMS_OUTPUT.PUT_LINE('Invalid mode for file: ' || i.object_name);
      WHEN UTL_FILE.READ_ERROR THEN
        DBMS_OUTPUT.PUT_LINE('Read error on file: ' || i.object_name);
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);
    END;
  END LOOP;

END;
/

The output is similar to data pump run anywhere else.

Starting "ADMIN"."SYS_EXPORT_SCHEMA_01":  
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
.  estimated "STRAVA"."MY_AREAS"                           950 MB
.  estimated "STRAVA"."ACTIVITIES"                       580.1 MB
.  estimated "STRAVA"."ACTIVITY_AREAS"                       5 MB
.  estimated "STRAVA"."WEBHOOK_EVENTS"                     640 KB
.  estimated "STRAVA"."GEAR"                               320 KB
.  estimated "STRAVA"."SCHEDULER$_JOB_ARG"                 320 KB
.  estimated "STRAVA"."API_LOG"                            128 KB
.  estimated "STRAVA"."MY_AREA_CODES"                      128 KB
.  estimated "STRAVA"."STRAVA_TOKENS"                       64 KB
.  estimated "STRAVA"."ACTIVITIES_EXT"                     4.7 KB
Total estimation using BLOCKS method: 1.5 GB
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ORACLE_OBJECT_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PASSWORD_HISTORY
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA/LOGREP
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/IDENTITY_COLUMN
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/INDEX/DOMAIN_INDEX/INDEX
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ/SCHEDULER
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA/SCHEDULER
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA/LBAC_EXP
. . exported "STRAVA"."MY_AREAS"                         384.5 MB  117584 rows
. . exported "STRAVA"."ACTIVITIES"                       347.9 MB    5614 rows
. . exported "STRAVA"."ACTIVITY_AREAS"                     1.2 MB  134817 rows
. . exported "STRAVA"."WEBHOOK_EVENTS"                     9.9 KB      32 rows
. . exported "STRAVA"."GEAR"                               6.4 KB      12 rows
. . exported "STRAVA"."SCHEDULER$_JOB_ARG"                 5.4 KB       4 rows
. . exported "STRAVA"."API_LOG"                            5.6 KB       4 rows
. . exported "STRAVA"."MY_AREA_CODES"                      5.8 KB      58 rows
. . exported "STRAVA"."STRAVA_TOKENS"                      5.2 KB       1 rows
. . exported "STRAVA"."ACTIVITIES_EXT"                       0 KB       0 rows
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
Master table "ADMIN"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ADMIN.SYS_EXPORT_SCHEMA_01 is:
  https://objectstorage.uk-london-1.oraclecloud.com/n/lr********ea/b/bucket-gofaster1/o/export_strava_20260512.dmp
Job "ADMIN"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue May 12 20:27:28 2026 elapsed 0 00:05:36

Move Log File to OCI

DBMS_CLOUD.PUT_OBJECT and DBMS_CLOUD.GET_OBJECT effectively copy to and from OCI storage.  I use DBMS_PUT_OBJECT to copy the data pump log files from the DATA_PUMP_DIR directory to my OCI bucket storage, then they can be deleted from the database directory with UTL_FILE.FREMOVE.

DECLARE 
  l_counter INTEGER := 0;
  l_filename VARCHAR2(100);
  l_dir VARCHAR2(100) := 'DATA_PUMP_DIR';
  l_uri VARCHAR2(200) := 'https://objectstorage.uk-london-1.oraclecloud.com/n/lr********ea/b/bucket-gofaster1/o/';

BEGIN
  FOR i IN (
    SELECT * FROM TABLE(DBMS_CLOUD.LIST_FILES('DATA_PUMP_DIR'))
    WHERE regexp_like(object_name,'export_strava.+\.(log|dmp)')
  ) LOOP
    l_counter := l_counter + 1;
    l_filename := REPLACE(i.object_name,'%T',TO_CHAR(i.created,'YYYYMMDD'));
    l_filename := REPLACE(l_filename,'%U',l_counter);
    dbms_output.put_line(l_filename);

    DBMS_CLOUD.PUT_OBJECT
    (object_uri      => l_uri||l_filename
    ,directory_name  => l_dir
    ,file_name       => i.object_name
    ,credential_name => 'OBJECT_STORE_CRED'
    );

    UTL_FILE.FREMOVE('DATA_PUMP_DIR',i.object_name);
  END LOOP;
END;
/

export_strava_20260512.log

Download Dump File Segments

The entire directory of dump files can be bulk downloaded with OCI CLI

oci os object bulk-download --bucket-name bucket-gofaster1 --download-dir C:\Users\david\OneDrive\Documents\strava\datapump --prefix export_strava_20260512.dmp_segments
Downloaded export_strava_20260512.dmp_segments/aaaaab  [####################################]  100%
Downloaded export_strava_20260512.dmp_segments/aaaaak  [####################################]  100%
Downloaded export_strava_20260512.dmp_segments/aaaaal  [####################################]  100%
...
Downloaded export_strava_20260512.dmp_segments/aaaabd  [####################################]  100%
Downloaded export_strava_20260512.dmp_segments/aaaaaz  [####################################]  100%
Downloaded export_strava_20260512.dmp_segments/aaaabf  [####################################]  100%

{
  "download-failures": {},
  "downloaded-objects": [
    "export_strava_20260512.dmp_segments/aaaaab",
    "export_strava_20260512.dmp_segments/aaaaak",
    "export_strava_20260512.dmp_segments/aaaaal",
...
    "export_strava_20260512.dmp_segments/aaaabd",
    "export_strava_20260512.dmp_segments/aaaaaz",
    "export_strava_20260512.dmp_segments/aaaabf"
  ],
  "skipped-objects": []
}

Data Pump to/from OCI Storage in Autonomous Database: General Considerations and Setup

This is the first of three blogs about using Data Pump on an Autonomous Oracle database.  

1. General considerations and setup.
2. Export
3. Import

Using Data Pump on an Autonomous Oracle database is a little different.  I have run into several limitations, so I'm sharing what I have learned.  Throughout the examples in these articles, I am using an Oracle 26 Autonomous Database on Oracle OCI Free Tier.

No Operating System Access

The first thing to bear in mind is that there is no operating system command-line access on the database server, so you cannot run the datapump clients expdp and impdp on the database server.  Instead, Data Pump jobs can be created through the PL/SQL API; dbms_datapump.  This will be demonstrated in the other two blog posts in this series.

Database Directory DATA_PUMP_DIR

On an autonomous database, there is no access to the underlying operating system or file system.  However, several database directories are created in the database.  Though write access is only granted to two of these directories.  In both cases, write access is granted to and can be granted on by the ADMIN and C##CLOUD$SERVICE users.

• DATA_PUMP_DIR is provided for the use of Data Pump.  Write access is also granted to roles EXP_FULL_DATABASE, IMP_FULL_DATABASE, DATAPUMP_CLOUD_EXP and DATAPUMP_CLOUD_IMP. 
• SQL_TCB_DIR is used by the SQL test case builder (see DBMS_SQLDIAG).  

However, these two directories point to database file systems.  

select directory_name, directory_path from dba_directories
where directory_name IN('DATA_PUMP_DIR','SQL_TCB_DIR')
order by directory_path
/

DIRECTORY_NAME  DIRECTORY_PATH                                              
--------------- ------------------------------------------------------------
DATA_PUMP_DIR  /u03/dbfs/4A03C84C700735C1E063D760000A3AE1/data/dpdump    
SQL_TCB_DIR    /u03/dbfs/4A03C84C700735C1E063D760000A3AE1/data/tcb_dir

Thus, their context exists within the database.  It is in a tablespace called DBFS_DATA, and it counts towards the total size of the container.

select tablespace_name, file_id, file_name, bytes, status
from dba_data_Files
where tablespace_name like 'DBFS%'
/
TABLESPACE_NAME    FILE_ID FILE_NAME                                               BYTES STATUS   
--------------- ---------- -------------------------------------------------- ---------- ---------
DBFS_DATA           10777 +DATA/FCEE691J/4A03C84C700735C1E063D760000A3AE1/DA 4399824896 AVAILABLE
                           TAFILE/dbfs_data.5025.1224350611

It is very easy to export to and import from these directories, but if you create a large dump file, the data files of the DBFS_DATA tablespace will extend and consume space in the database container.  In an autonomous database, no user has sufficient access to shrink the tablespace by conventional means.  

In the paid Autonomous Database, it can be released using OCI CLI (see shrink command reference).  This is not available in the free tier.

Setting Up OCI Command Line Interface

Create a pair of public and private keys in the command line interface.

oci setup keys

File C:\Users\david\.oci\oci_api_key_public.pem already exists, do you want to overwrite? [y/N]: N
File C:\Users\david\.oci\oci_api_key.pem already exists, do you want to overwrite? [y/N]: N
Public key fingerprint: 72:7a:3d:1b:66:65:cf:a1:ed:2a:3d:42:6f:03:53:0b

Then follow the instructions How to Upload the Public Key to upload the public key to the Tokens and Keys section of OCI

Take the information in the configuration file preview and paste it back into the OCI configuration file

This is in the same location as the public and private key files, in my case C:\Users\david\.oci\config.

Specify the fully qualified path and name of the private key file.

[DEFAULT]
user=ocid1.user.oc1..aaaaaaaaohj2suignixq7jjyrbkodaz5r73wonx25pzaspfpmkiyg76sra77
fingerprint=05:d4:fc:e2:81:9e:18:fb:00:d5:2b:9e:12:34:56:78
key_file=C:\Users\david\.oci\oci_api_key.pem
tenancy=ocid1.tenancy.oc1..aaaaaaaaf5e7vjqhg6m3jiv3zdos3b2hkbixqyq3tvqhtuyftyemzejzwosv
region=uk-london-1

Create Credential

Finally, create a credential with access to the OCI storage bucket that will hold the datapump files.

BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL (
      credential_name   => 'OBJECT_STORE_CRED',
      username          => 'OracleIdentityCloudService/dba@go-faster.co.uk',
      password          => ';I)<P+3U:GRi6mprPD1n'
  );
END;
/

Default Credential

I have found it convenient to specify a default credential.

ALTER DATABASE PROPERTY SET DEFAULT_CREDENTIAL = 'ADMIN.OBJECT_STORE_CRED';

For example, I can query the OCI Bucket without having to specify a credential to access the OCI Bucket storage every time.

SELECT object_name, bytes/1024/1024 Mb
FROM table(
  DBMS_CLOUD.LIST_objects
  (location_uri => 'https://objectstorage.uk-london-1.oraclecloud.com/n/lrp********a/b/bucket-gofaster1/o/'));

OBJECT_NAME                                                        MB
------------------------------------------------------------ --------
export_strava_20260506.dmp                                      0.000
export_strava_20260506.dmp_segments/aaaaaa                      2.000
export_strava_20260506.dmp_segments/aaaaab                     24.000
...
export_strava_20260506.dmp_segments/aaaabf                     11.563
export_strava_20260506.log                                      0.004

The next blog describes how to create data pump jobs to export data.