EXPORT AND IMPORT DATAPUMP IN REMAP_DATA & REMAP_SCHEMA

EXPORT AND IMPORT DATA PUMP IN REMAP_DATA & REMAP_SCHEMA

In this blog we are going to learn about EXPORT AND IMPORT in data pump using remap data and remap schema parameters.

Remap_data:

This Remap_data, in export and import operations, the Remap_data parameter allows you to associate a remap packaged function that will accept the column value as a parameter and return a modified version of the data.

Remap_schema:

Many times you are required to move a schema from one database to another. Most likely, this happens when you have a schema developed in a test box with all the required objects that need to be imported into the production environment. Also, sometimes objects within the same database need to be moved from one schema to another.

For an example i have shown below export and import operation. Here, i have create a package for

SQL> create or replace package record_sam

2 as

3 function record2_sam(mobile_no in number) return number;

4 end;

5 /

Package created.

SQL> create or replace package body rec

2 ^C

SQL> create or replace package body rec_sam

2 ^C

SQL> create or replace package body record_sam

2 as function record2_sam(mobile_no in number) return number

3 as

4 new_num number(10);

5 begin

6 new_num:= mobile_no + 10;

7 return new_num;

8 end;

9 end;

10 /

Package body created.

SQL> commit;

Commit complete.

SQL> !

[oracle@oracle ~]$ expdp directory=parexp dumpfile=remapdata.dmp logfile=remapdata.log tables=shan2.records2 remap_data=shan2.records2.mobile_no:shan2.record_sam.record2_sam

Export: Release 12.2.0.1.0 – Production on Sat Jan 18 23:21:58 2020

Username: shan2

Password:

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

Starting “SHAN2”.”SYS_EXPORT_TABLE_01″: shan2/******** directory=parexp dumpfile=remapdata.dmp logfile=remapdata.log tables=shan2.records2 remap_data=shan2.records2.mobile_no:shan2.record_sam.record2_sam

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

Processing object type TABLE_EXPORT/TABLE/TABLE

. . exported “SHAN2”.”RECORDS2″ 6.148 KB 9 rows

Master table “SHAN2”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded

******************************************************************************

Dump file set for SHAN2.SYS_EXPORT_TABLE_01 is:

/u01/exppar/remapdata.dmp

Job “SHAN2”.”SYS_EXPORT_TABLE_01″ successfully completed at Sat Jan 18 23:22:20 2020 elapsed 0 00:00:13

[oracle@oracle ~]$ impdp directory=parexp dumpfile=remapdata.dmp logfile=impremap.log remap_schema=shan2:shan1

Import: Release 12.2.0.1.0 – Production on Sat Jan 18 23:26:25 2020

Username: shan1

Password:

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

Master table “SHAN1”.”SYS_IMPORT_FULL_01″ successfully loaded/unloaded

Starting “SHAN1”.”SYS_IMPORT_FULL_01″: shan1/******** directory=parexp dumpfile=remapdata.dmp logfile=impremap.log remap_schema=shan2:shan1

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported “SHAN1”.”RECORDS2″ 6.148 KB 9 rows

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

Job “SHAN1”.”SYS_IMPORT_FULL_01″ successfully completed at Sat Jan 18 23:27:00 2020 elapsed 0 00:00:12

[oracle@oracle ~]$ !sq

sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jan 18 23:27:09 2020

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SQL> conn shan1/dba1

Connected.

SQL> select * from records2;

ID NAME MOBILE_NO

———- ——————– ———-

1 zzzzzzz 8997897843

9 rows selected.

SQL> conn shan2/dba2;