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