Sunday, May 17, 2026

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
Posted by at
Labels: , ,

No comments :

Post a Comment

Subscribe to: Post Comments ( Atom )