How to Export and Import Using DBMS_DATAPUMP - Remap Schema Version

Introduction

This procedure takes a schema in the current database, in this case master_owner, and exports it down into the DATA_PUMP_DIR location. It then imports the schema to the same database and remaps master_owner to the schema specified in the procedure call. This is useful for creating development schemas based on a master schema.

Code

CREATE OR REPLACE PROCEDURE dba_expdp(p_schema VARCHAR2)
IS
 
    v_handle NUMBER;
    v_ejname VARCHAR2 (100);
    v_ijname VARCHAR2 (100);
    v_dir VARCHAR2 (100);
    v_fname VARCHAR2 (100);
    v_date VARCHAR2 (100);
    v_elog VARCHAR2 (100);
    v_ilog VARCHAR2 (100);
    v_ejstatus VARCHAR2 (100);
    v_ijstatus VARCHAR2 (100);
 
BEGIN
 
    v_date := to_char(sysdate,'YYYYMMDDHH24MISS');
    v_fname := 'export_'||p_schema||'_'||v_date||'.dmp';
    v_elog := 'export_'||p_schema||'_'||v_date||'.log';
    v_ilog := 'export_'||p_schema||'_'||v_date||'.log';
    v_ejname := 'export_'||p_schema||'_'||v_date;
    v_ijname := 'import_'||p_schema||'_'||v_date;
    v_dir := 'DATA_PUMP_DIR';
 
-- Export Schema
 
    v_handle := dbms_datapump.open(operation => 'EXPORT', job_mode => 'SCHEMA', job_name => v_ejname);
    dbms_datapump.add_file(handle => v_handle, filename => v_fname, directory => v_dir, filetype => 1);
    dbms_datapump.add_file(handle => v_handle, filename => v_elog, directory => v_dir, filetype => 3);
    dbms_datapump.metadata_filter (handle => v_handle, name => 'SCHEMA_EXPR', value => '=''MASTER_OWNER''');
    dbms_datapump.start_job(v_handle);
    dbms_datapump.wait_for_job(v_handle,v_ejstatus);
    dbms_datapump.detach(v_handle);
 
-- Import Schema   
 
    v_handle := dbms_datapump.open(operation => 'IMPORT',job_mode => 'SCHEMA', remote_link => null, job_name => v_ijname, version => 'LATEST');
    dbms_datapump.add_file(handle => v_handle, filename  => v_fname, directory => 'DATA_PUMP_DIR');
    dbms_datapump.add_file(handle => v_handle, filename  => v_ilog, directory => 'DATA_PUMP_DIR', filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
    dbms_datapump.metadata_remap(handle => v_handle, name => 'REMAP_SCHEMA', old_value  => 'MASTER_OWNER', value => p_schema);
    dbms_datapump.start_job(v_handle);
    dbms_datapump.wait_for_job(v_handle,v_ijstatus);
    dbms_datapump.detach(v_handle);   
 
END;

Published 29th September 2022

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License