dbms_datapump examples

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

 
Doing the import this way can be done straight over a database link so one doesn’t even need to do an export.

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/

 

 

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s