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