Thursday, May 14, 2026

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": []
}

Posted by at
Labels: , ,

No comments :

Post a Comment

Subscribe to: Post Comments ( Atom )