notes/tiddlywiki/Using DATAPUMP through PL_SQL examples.md

Note based on Data Pump API for PL/SQL (DBMS_DATAPUMP)

Import

Schema import

impdp command line

impdp userid=superhero/***** \
    dumpfile=MYDUMP:EMUSER.dmp logfile=MYDUMP:impo1.log \
    remap_schema=EMUSER:APP_USER \
    remap_tablespace=EM_DATA:APP_TS

PL/SQL block

declare
  l_dp_handle       number;
begin
  -- Open a schema import job.
  l_dp_handle := dbms_datapump.open(
    operation   => 'IMPORT',
    job_mode    => 'SCHEMA',
    remote_link => NULL,
    job_name    => 'EMUSER_IMPORT',
    version     => 'LATEST');

  -- Specify the schema to be imported.
  dbms_datapump.metadata_filter(
    handle => l_dp_handle,
    name   => 'SCHEMA_EXPR',
    value  => '= ''EMUSER''');

  -- Specify the dump file name and directory object name.
  dbms_datapump.add_file(
    handle    => l_dp_handle,
    filename  => 'EMUSER.dmp',
    directory => 'MYDUMP');

  -- Specify the log file name and directory object name.
  dbms_datapump.add_file(
    handle    => l_dp_handle,
    filename  => 'impo1.log,
    directory => 'MYDUMP',
    filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);

  -- Perform a REMAP_SCHEMA
  dbms_datapump.metadata_remap(
    handle     => l_dp_handle,
    name       => 'REMAP_SCHEMA',
    old_value  => 'EMUSER',
    value      => 'APP_USER');

   -- Perform a REMAP_TABLESPACE
  dbms_datapump.metadata_remap(
    handle     => l_dp_handle,
    name       => 'REMAP_TABLESPACE',
    old_value  => 'EM_DATA',
    value      => 'APP_TS');   

  dbms_datapump.start_job(l_dp_handle);

  dbms_datapump.detach(l_dp_handle);
end;
/

Multiple schema import

impdp command line

impdp userid=superhero/***** \
    dumpfile=MYDUMP:GREEDOPRD_%U.dmp logfile=MYDUMP:impo.log \
    schemas=APP_USER,REPO_USER\
    remap_schema=APP_USER:ALPHA,REPO_USER:OMEGA \
    remap_tablespace=APP_TS:TS_ALPHA,REPO_TS:TS_OMEGA

PL/SQL block

declare
  l_dp_handle       number;
begin
  -- Open a schema import job.
  l_dp_handle := dbms_datapump.open(
    operation   => 'IMPORT',
    job_mode    => 'SCHEMA',
    remote_link => NULL,
    job_name    => 'MULTIPLE_SCHEMAS_IMPORT',
    version     => 'LATEST');

  -- Specify the schema to be imported.
  dbms_datapump.metadata_filter(
    handle => l_dp_handle,
    name   => 'SCHEMA_EXPR',
    value  => 'in (''APP_USER'',''REPO_USER'')');

  -- Specify the dump file name and directory object name.
  dbms_datapump.add_file(
    handle    => l_dp_handle,
    filename  => 'GREEDOPRD_%U.dmp',
    directory => 'MYDUMP');

  -- Specify the log file name and directory object name.
  dbms_datapump.add_file(
    handle    => l_dp_handle,
    filename  => 'impo.log',
    directory => 'MYDUMP',
    filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);

  -- Perform a REMAP_SCHEMA
  dbms_datapump.metadata_remap(
    handle     => l_dp_handle,
    name       => 'REMAP_SCHEMA',
    old_value  => 'APP_USER',
    value      => 'ALPHA');

  dbms_datapump.metadata_remap(
    handle     => l_dp_handle,
    name       => 'REMAP_SCHEMA',
    old_value  => 'REPO_USER',
    value      => 'OMEGA');


   -- Perform a REMAP_TABLESPACE
  dbms_datapump.metadata_remap(
    handle     => l_dp_handle,
    name       => 'REMAP_TABLESPACE',
    old_value  => 'APP_TS',
    value      => 'TS_ALPHA');   

    dbms_datapump.metadata_remap(
    handle     => l_dp_handle,
    name       => 'REMAP_TABLESPACE',
    old_value  => 'REPO_TS',
    value      => 'TS_OMEGA');   
  
  dbms_datapump.set_parallel(l_dp_handle,2);

  dbms_datapump.start_job(l_dp_handle);

  dbms_datapump.detach(l_dp_handle);
end;
/

Export

FULL database export

expdp command line

expdp userid=superhero/***** \
    dumpfile=MYDUMP:GREEDOPRD_%U.dmp logfile=MYDUMP:GREEDOPRD.log \
    full=Y \
    flashback_time=systimestamp \
    parallel=2

PL/SQL block

declare
  l_dp_handle       number;
begin
  -- Open a full export job.
  l_dp_handle := dbms_datapump.open(
    operation   => 'EXPORT',
    job_mode    => 'FULL',
    remote_link => NULL,
    job_name    => 'GREEDOPRD_FULL_EXP',
    version     => 'LATEST');

  -- Specify the dump file name and directory object name.
  dbms_datapump.add_file(
    handle    => l_dp_handle,
    filename  => 'GREEDOPRD_%U.dmp',
    directory => 'MYDUMP');

  -- Specify the log file name and directory object name.
  dbms_datapump.add_file(
    handle    => l_dp_handle,
    filename  => 'GREEDOPRD.log',
    directory => 'MYDUMP',
    filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);

  dbms_datapump.set_parameter(l_dp_handle,'CLIENT_COMMAND','Full Consistent Data Pump Export with PARALLEL 2');
  dbms_datapump.set_parameter(l_dp_handle,'FLASHBACK_TIME','SYSTIMESTAMP');
  dbms_datapump.set_parallel(l_dp_handle,2);

  dbms_datapump.start_job(l_dp_handle);

  dbms_datapump.detach(l_dp_handle);
end;
/