1. Using dbms_datapump for import
http://potoracle.blogspot.com/2007/04/using-dbmsdatapump-for-import.html
MONDAY, 16 APRIL 2007
Using dbms_datapump for import
create or replace procedure dwh_import
as
ind NUMBER; — Loop index number
jobhandle NUMBER; — Data Pump job handle
l_job_name varchar2(100); — Job Name
percent_done NUMBER; — Percentage of job complete
job_state VARCHAR2(30); — Keeps track of job state
le ku$_LogEntry; — work-in-progress and error messages
js ku$_JobStatus; — Job status from get_status
sts ku$_Status; — Status object returned by get_status
BEGIN
l_job_name := ‘DWHIMPORT’to_char(sysdate,’YYYYMMDDHH24MISS’);
jobhandle := DBMS_DATAPUMP.OPEN(operation => ‘IMPORT’,
job_mode => ‘TABLE’,
remote_link => ‘APOLLO’,
job_name => l_job_name,
version => ‘COMPATIBLE’);
dbms_output.put_line(‘jobhandle is : ‘jobhandle);
DBMS_DATAPUMP.add_file( handle => jobhandle,
filename => ‘DWHIMPORT.log’,
directory => ‘DATAPUMPX’,
filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE
);
dbms_output.put_line(‘Import Log file is created..’);
DBMS_DATAPUMP.SET_PARALLEL(jobhandle, 1);
dbms_output.put_line(‘parallel set to 1’);
DBMS_DATAPUMP.METADATA_REMAP(handle => jobhandle,
name => ‘REMAP_SCHEMA’,
old_value => ‘WD_USER’,
value => ‘BI_USER’);
dbms_output.put_line(‘metadat_remap’);
DBMS_DATAPUMP.SET_PARAMETER(jobhandle,’TABLE_EXISTS_ACTION’,’REPLACE’);
dbms_output.put_line(‘set_parameter’);
— dbms_datapump.metadata_filter (handle => my_handle, name => ‘NAME_EXPR’,
— value => ‘LIKE ”DWH_%”’, object_type => ‘TABLE’);
DBMS_DATAPUMP.metadata_filter( handle => jobhandle,
name => ‘NAME_EXPR’,
value => ‘IN (”DWH_CANVASS”,”DWH_CANVASS_DATE”,”DWH_CHANNEL”,
”DWH_CUSTOMER”,”DWH_CYCLE_DATE”, ”DWH_DATE”,
”DWH_DOS_APPOINTMENTS”,”DWH_DOS_SALESREP_TARGET”,
”DWH_DOS_STATE_OF_PLAY”,”DWH_DOS_TEAM”,”DWH_GG_ICANVASS”,
”DWH_GG_WD_CHANNEL”,”DWH_GG_WD_PRODUCTS”,”DWH_GP_WD_CHANNEL”,
”DWH_GP_WD_PRODUCTS”,”DWH_MANDAYS_ACTUALS”,
”DWH_MANDAYS_PLANNED”,”DWH_PA_WD_CHANNEL”,”DWH_PA_WD_PRODUCTS”,
”DWH_PM_WD_CHANNEL”,”DWH_PM_WD_PRODUCTS”,”DWH_PRODUCT”,
”DWH_REP”,”DWH_SALES”,”DWH_SALES_STATUS”,”DWH_TARGET”,
”DWH_UNIT”,”DWH_WD_CANVASS_GROUPS”,”DWH_WD_CHANNEL”,
”DWH_WD_CHANNEL_GRP”,”DWH_WD_DAYOFF”,”DWH_WD_FINANCE_MONTH”,
”DWH_WD_PRODUCT”,”DWH_REFRESH_DATES”,”DWH_MTG_TARGET”,”DWH_DAY_TARGET_ALL”,
”DWH_DAY_TARGET_SALES”,”DWH_DAY_TARGET_CANV”)’);
dbms_output.put_line(‘Name Export’);
dbms_datapump.metadata_remap(handle => jobhandle,
name => ‘REMAP_TABLESPACE’,
old_value => ‘DWH_TAB_SMALL’,
value => ‘BI_USER_TS’);
dbms_datapump.metadata_remap(handle => jobhandle,
name => ‘REMAP_TABLESPACE’,
old_value => ‘DWH_TAB_LARGE’,
value => ‘BI_USER_TS’);
dbms_datapump.metadata_remap(handle => jobhandle,
name => ‘REMAP_TABLESPACE’,
old_value => ‘DWH_IND_LARGE’,
value => ‘USERS’);
dbms_datapump.metadata_remap(handle => jobhandle,
name => ‘REMAP_TABLESPACE’,
old_value => ‘DWH_IND_SMALL’,
value => ‘USERS’);
DBMS_DATAPUMP.SET_PARAMETER(jobhandle,’ESTIMATE’,’STATISTICS’);
dbms_output.put_line(‘Estimate ‘);
DBMS_DATAPUMP.START_JOB(jobhandle);
dbms_output.put_line(‘Job Started’);
percent_done := 0;
job_state := ‘UNDEFINED’;
WHILE (job_state != ‘COMPLETED’) and (job_state != ‘STOPPED’) LOOP
DBMS_DATAPUMP.get_status(jobhandle,
DBMS_DATAPUMP.ku$_status_job_error +
DBMS_DATAPUMP.ku$_status_job_status +
DBMS_DATAPUMP.ku$_status_wip,-1,job_state,sts);
js := sts.job_status;
IF js.percent_done != percent_done THEN
DBMS_OUTPUT.PUT_LINE(‘*** Job percent done = ‘ to_char(js.percent_done));
percent_done := js.percent_done;
END IF;
IF (bitand(sts.mask,dbms_datapump.ku$_status_wip) != 0) THEN
le := sts.wip;
ELSE
IF (bitand(sts.mask,dbms_datapump.ku$_status_job_error) != 0) THEN
le := sts.error;
ELSE
le := null;
END IF;
END IF;
IF le IS NOT NULL THEN
ind := le.FIRST;
WHILE ind IS NOT NULL LOOP
DBMS_OUTPUT.PUT_LINE(le(ind).LogText);
ind := le.NEXT(ind);
END LOOP;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(‘Job has completed’);
DBMS_OUTPUT.PUT_LINE(‘Final job state = ‘ job_state);
DBMS_DATAPUMP.DETACH(jobhandle);
END;
2. The gains and pains of dbms_datapump.
http://danirey.wordpress.com/tag/dbms_datapump/
mkjay 