Example for REMAP_DATA in datapump job with EXPDP or IMPDP

Example for REMAP_DATA in datapump job with EXPDP or IMPDP

REMAP_DATA is used to do modified data during taking dumps and importing dumps files into the database.
It act like as TRANSFORMATION process of data during transfer or also used to clean data during import. Example cleaning space from front and last from data.

REMAP_DATA syntax:
REMAP_DATA=[schema.]tablename.column_name:[schema.]pkg.function

Note: it use the package, not simple function

EXPDP example of using REMAP_DATA
We have HR schema and having table TEST2. We are creating new table in example while IMPDP with REMAP_TABLE parameter.
We create a package with function in it which will add 100 to id value of TEST2 table as show in example below.


--- Created package which add 100 in function as following example:

CREATE or Replace PACKAGE adds AS
FUNCTION adder(v_id number) return number;
END adds;
/

CREATE OR REPLACE PACKAGE BODY adds AS
function adder(v_id number)
return number
is
v_id1 number;
begin
v_id1 := v_id + 100;
return v_id1;
end adder;
END adds;
/

-- Check TEST2 table
SQL> select * from test2;

ID
----------
1
2
3

-- Use REMAP_DATA in EXPDP example:
expdp directory=dbbackup dumpfile=test2export.dmp logfile=test2export.log remap_data=hr.test2.id:hr.adds.adder tables=hr.test2

-- Import the data of dump file which is converted the value of ID column in EXPDP
impdp directory=dbbackup dumpfile=test2export.dmp logfile=test2import.log remap_table=test2:testexp

SQL> select * from testEXP;

ID
----------
101
102
103

IMPDP example of using REMAP_DATE
Create the package as you created above, we use same packages for example.

-- Check table test2
SQL> select * from test2;

ID
----------
1
2
3

-- Take simple export of test2
EXPDP directory=dbbackup dumpfile=test2.dmp logfile=test2exp.log tables=hr.test2

--Use REMAP_DATE with impdp now
impdp directory=dbbackup dumpfile=test2.dmp logfile=test2imp.log remap_table=test2:testimp remap_data=test2.id:hr.adds.adder

--Check TESTIMP create with IMPDP
SQL> select * from testimp;

ID
----------
101
102
103

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.