Oracle DBMS_DATAPUMP and MySQL Integration with Amazon S3

Oracle Usage

The DBMS_DATAPUMP package provides Oracle Data Pump functionality that can be run within the database.

The DBMS_DATAPUMP package subprograms are:

Examples

The following example shows how to export the HR schema. It assumes all directories have already been created and the user has all required privileges.

DECLARE
loopidx NUMBER;
job_handle NUMBER;
percent_done NUMBER;
job_state VARCHAR2(30);
err ku$_LogEntry;
job_status ku$_JobStatus;
job_desc ku$_JobDesc;
obj_stat ku$_Status;
BEGIN

job_handle := DBMS_DATAPUMP.OPEN('EXPORT','SCHEMA',NULL,'EXP_SAMP','LATEST');

DBMS_DATAPUMP.ADD_FILE(job_handle,'hr.dmp','DMPDIR');

DBMS_DATAPUMP.METADATA_FILTER(job_handle,'SCHEMA_EXPR','IN (''HR'')');

DBMS_DATAPUMP.START_JOB(job_handle);

percent_done := 0;
job_state := 'UNDEFINED';
while (job_state != 'COMPLETED') and (job_state != 'STOPPED') loop
dbms_datapump.get_status(job_handle,
dbms_datapump.ku$_status_job_error +
dbms_datapump.ku$_status_job_status +
dbms_datapump.ku$_status_wip,-1,job_state,obj_stat);
job_status := obj_stat.job_status;

/* HERE YOU CAN PRINT THE STATUS */

if job_status.percent_done != percent_done then
  percent_done := job_status.percent_done;
end if;

if (bitand(obj_stat.mask,dbms_datapump.ku$_status_wip) != 0) then
  err := obj_stat.wip;
else
  if (bitand(obj_stat.mask,dbms_datapump.ku$_status_job_error) != 0)
  then
    err := obj_stat.error;
    else
    err := null;
  end if;
end if;

if err is not null then
  loopidx := err.FIRST;
  while loopidx is not null loop
    loopidx := err.NEXT(loopidx);
  end loop;
end if;
end loop;

dbms_datapump.detach(job_handle);
END;
/

For more information, see Overview of Oracle Data Pump in the Oracle documentation.

MySQL Usage

There is no feature in MySQL fully equivalent to the Oracle DBMS_DATAPUMP package, but there are tools and features that achieve the same functionality.

To export data from the database to the file system, use the SELECT INTO OUTFILE S3 command. To import data from the filesystem, use the LOAD DATA FROM S3 command.

To achieve the most functionality, this feature can be mixed with metadata tables and events to handle the operations.

For more information, see Oracle External Tables and MySQL Integration with Amazon S3.

Summary