Use Data Pump from PL/SQL with DBMS_DATAPUMP

Use the DBMS_DATAPUMP PL/SQL package to export and import Data Pump dump files directly to and from Object Storage.

The DBMS_DATAPUMP PL/SQL package provides an interface for Oracle Data Pump export and import operations directly from within the database. DBMS_DATAPUMP is suitable for full and schema-level exports and imports. For exporting or loading query-level or object-level data, consider using DBMS_CLOUD.EXPORT_DATA or DBMS_CLOUD.COPY_DATA. For full database-level import or export using the command-line utilities, use the expdp and impdp Data Pump tools.

This topic describes how to use the DBMS_DATAPUMP PL/SQL package to export and import Data Pump dump files directly to and from an object store. To write or read an object store file, specify an object store URI in ADD_FILE and supply a credential name.

Prerequisites

Create Object Storage credentials, network ACLs, and any required directory or credential objects before following these examples.

Example: Export to Object Storage

This example creates an export job, writes the dump file to Object Storage, and uses the credential name in place of a local directory object.

DECLARE
  hdl       NUMBER;
  dump_file VARCHAR2(1024) := 'https://objectstorage.example.com/n/namespace/b/bucket/o/hr_exp_%U.dmp';
  cred_name VARCHAR2(128)  := 'OBJSTORE_CRED';
BEGIN
  hdl := DBMS_DATAPUMP.OPEN(
           operation => 'EXPORT',
           job_mode   => 'SCHEMA',
           job_name   => 'HR_EXP_OBJSTORE');

  DBMS_DATAPUMP.ADD_FILE(
    handle    => hdl,
    filename  => dump_file,
    directory => cred_name,
    filesize  => NULL,
    filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_URIDUMP_FILE,
    reusefile => 1);

  DBMS_DATAPUMP.METADATA_FILTER(hdl, 'SCHEMA_EXPR', 'IN (''HR'')');
  DBMS_DATAPUMP.START_JOB(hdl);
  DBMS_DATAPUMP.DETACH(hdl);
END;
/

Example: Import from Object Storage

This example imports the dump file from Object Storage and remaps the schema during import.

DECLARE
  hdl       NUMBER;
  dump_file VARCHAR2(1024) := 'https://objectstorage.example.com/n/namespace/b/bucket/o/hr_exp_%U.dmp';
  cred_name VARCHAR2(128)  := 'OBJSTORE_CRED';
BEGIN
  hdl := DBMS_DATAPUMP.OPEN(
           operation => 'IMPORT',
           job_mode   => 'SCHEMA',
           job_name   => 'HR_IMP_OBJSTORE');

  DBMS_DATAPUMP.ADD_FILE(
    handle    => hdl,
    filename  => dump_file,
    directory => cred_name,
    filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_URIDUMP_FILE);

  DBMS_DATAPUMP.METADATA_REMAP(hdl, 'REMAP_SCHEMA', 'HR', 'HR2');
  DBMS_DATAPUMP.START_JOB(hdl);
  DBMS_DATAPUMP.DETACH(hdl);
END;
/