oracle-databaseremapexpdp

Altering row data while using Oracle expdp utility


I'm a developer -not a DBA or DB expert - but as part of my job I should be able to export data to get it imported into another DB. While exporting I need to be able to alter row data that is going into dump files. Is that possible? Let's say I have a column ID with value 1,2,3,4... and while exporting I should be able to do ID * -1 for the result data -1,-2,-3,-4.... This would help me in migrating data when there's data already present in the target DB.


Solution

  • You can use the expdp REMAP_DATA option to do this, assuming you can create a package and function to do the conversion:

    With some simple data:

    create table t42 (id number);
    
    insert into t42 select level from dual connect by level <= 5;
    
    select * from t42;
    
            ID
    ----------
             1
             2
             3
             4
             5
    

    ... and a package function:

    create package p42 as
        function negate(p_number in number) return number;
    end p42;
    /
    
    create package body p42 as
        function negate(p_number in number) return number is
        begin
            return -1 * p_number;
        end;
    end p42;
    /
    

    You can export with remap_data, and then import, in this test back into the same DB/schema:

    expdp directory=data_pump_dir dumpfile=d42.dmp nologfile=yes tables=t42 \
        remap_data=t42.id:p42.negate
    impdp directory=data_pump_dir dumpfile=d42.dmp nologfile=yes tables=t42 \
        content=data_only
    

    The table now has:

    select * from t42;
    
            ID
    ----------
             1
             2
             3
             4
             5
            -1
            -2
            -3
            -4
            -5
    

    You can do the same modification on import as well, using the impdp REMAP_DATA parameter instead. The principle is the same, just depends at what point you want to modify the data.