How to Use DBMS_DATAPUMP with a DB Link
Introduction
This procedure takes a schema in the current database, in this case master_owner, and uses a DB Link to export and import it into the same database. It also remaps master_owner to the schema specified in the procedure call. This is useful for creating development schemas based on a master schema on databases with little storage to store an external file.
Code for DB Link
create public database link "DBA_SELF_LINK" connect to <user performing the import> identified by "<password>" using '<MyDbHost:1521/<MyDbService>';
Code for Import
CREATE OR REPLACE PROCEDURE dba_impdp_link(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); v_link VARCHAR (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'; v_link := 'DBA_SELF_LINK'; -- Import Schema v_handle := dbms_datapump.open(operation => 'IMPORT',job_mode => 'SCHEMA', remote_link => v_link, job_name => v_ijname, version => 'LATEST'); dbms_datapump.metadata_filter (handle => v_handle, name => 'SCHEMA_EXPR', value => '=''MASTER_OWNER'''); 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 5th October 2022
