As you know Datapump is the Oracle preferred tool for moving data and is soon will be the only option because traditional exp/imp utilities will be deprecated. In following sections we will look at how you can use schema, table and data remapping to get more out of this powerful utility.
Data remapping allows you to manipulate sensitive data before actually placing the data inside the dump file. This can happen on done at different stages including during schema remap, table remap and remapping of individual rows inside tables i.e. data remapping. We will look at them one by one in this section.
Data remapping allows you to manipulate sensitive data before actually placing the data inside the dump file. This can happen on done at different stages including during schema remap, table remap and remapping of individual rows inside tables i.e. data remapping. We will look at them one by one in this section.
Schema Remapping
When you export a schema or some objects of a schema from one database and import it to the other then import utility expects the same schema to be present in second database. For example if you export EMP table of SCOTT schema and import it to another then import utility will try to locate the SCOTT schema in second database and if not present, it may create it for you depending on the options you specified.
But if you want to create the EMP table in SH schema instead. The remap_schema option of impdp utility will allow you to accomplish that. For example
$ impdp userid=rman/rman@orcl dumpfile=data_pump:SCOTT.dmp remap_schema=SCOTT:SH
This functionality of was available in before 11g versions of database but had a different syntax and naming which has been changed in favor of more broader concept that goes beyond just schema mapping.
Table Remapping
On similar grounds you can also import data from one table into a table with a different name by using the REMAP_TABLE option. If you want to import data of EMP table to EMPTEST table then you just have to provide the REMAP_TABLE option with the new table name.
This option can be used for both partitioned and nonpartitioned tables.
On the other side however table remapping has the following restrictions.
- If partitioned tables were exported in a transportable mode then each partition or subpartition will be moved to a separate table of its own.
- Tables will not be remapped if they already exist even if you specify the TABLE_EXIST_ACTION to truncate or append.
- The export must be performed in non transportable mode.
The syntax of REMAP_TABLE is as follows:
REMAP_TABLE=[old_schema_name.old_table_name]:[new_schema_name.new_table_name]Data Remapping
This remapping option is used to remap data rows which is an extremely powerful feature. You can modify the rows while exporting or importing them. It is worth mentioning that as opposed to the schema and table level remapping which is only a logical mapping to happen at import time, data remapping can be done while creating the dump file i.e. expdp or it can be done while importing a dump file i.e. impdp.
To use this all you have to do is to create a function to perform the actual manipulation and wrap it inside a package and then pass the name when exporting data or importing data. The decision on when to use this depends on your requirement. If you want to store manipulated data inside the dump file then you can use it while exporting and if you don’t then you may use it while importing.
We will now look at an example to test this functionality. Let’s take the example of the EMP table inside the SCOTT schema. During the export process we will set the salary column value to a fix value of 5000. Firstly we create the package to actually perform this operation.
SQL> create or replace package edba_remap
2 is<
3 function sal_remap (p_sal number) return number;
4 end;
5 /
Package created.Elapsed: 00:00:00.20
SQL> create or replace package body edba_remap
2 is
3 function sal_remap (p_sal number) return number
4 as
5 v_sal number := 5000;
6 begin
7 return v_sal;
8 end;
9 end;
10 /
Package body created.
2 is
3 function sal_remap (p_sal number) return number
4 as
5 v_sal number := 5000;
6 begin
7 return v_sal;
8 end;
9 end;
10 /
Package body created.
Elapsed: 00:00:00.08
Once we have the function to actually modify the salary column, we will export the table and provide the function name for data remapping. The REMAP_DATA option expects a schema_name.table_name.column_name and then a colon (:) followed by package and function name.
Let’s start the export using the following command.
$ expdp userid=rman/rman dumpfile=data_pump_dir:EMP_remap.dmp tables=SCOTT.EMP REMAP_DATA=SCOTT.EMP.sal:edba_remap.sal_remapExport: Release 11.2.0.3.0 - Production on Wed Mar 20 01:52:17 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "RMAN"."SYS_EXPORT_TABLE_01": userid=rman/******** dumpfile=data_pump_dir:EMP_remap.dmp tables=SCOTT.EMP REMAP_DATA=SCOTT.EMP.sal:edba_remap.sal_remap
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."EMP" 8.554 KB 14 rows
Master table "RMAN"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
**************************************************************
Dump file set for RMAN.SYS_EXPORT_TABLE_01 is:
/u01/app/Oracle/admin/orcl/dpdump/EMP_remap.dmp
Job "RMAN"."SYS_EXPORT_TABLE_01" successfully completed at 01:52:33
Now we will drop the original EMP table after selecting it once so that we know what table we are viewing.
SQL> select * from SCOTT.EMP;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---- ------ ---------- --------- ---------- --------- ----- -------7369 SMITH CLERK 7902 17-DEC-80 800 207499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 307521 WARD SALESMAN 7698 22-FEB-81 1250 500 307566 JONES MANAGER 7839 02-APR-81 2975 207654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 307698 BLAKE MANAGER 7839 01-MAY-81 2850 307782 CLARK MANAGER 7839 09-JUN-81 2450 107788 SCOTT ANALYST 7566 19-APR-87 3000 207839 KING PRESIDENT 17-NOV-81 5000 107844 TURNER SALESMAN 7698 08-SEP-81 1500 0 307876 ADAMS CLERK 7788 23-MAY-87 1100 207900 JAMES CLERK 7698 03-DEC-81 950 307902 FORD ANALYST 7566 03-DEC-81 3000 207934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
Elapsed: 00:00:00.12
As you can see the sal column contains the original values. For testing purposes we will now drop this table and will import it from dump file we have created above. The sal column should have value of 5000 for all employees after the import.
SQL> drop table SCOTT.EMP;Table dropped.Elapsed: 00:00:00.18
Now we will import the dump file.
$ impdp userid=rman/rman dumpfile=EMP_remap.dmp tables=SCOTT.EMPImport: Release 11.2.0.3.0 - Production on Wed Mar 20 02:00:31 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "RMAN"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "RMAN"."SYS_IMPORT_TABLE_01": userid=rman/******** dumpfile=EMP_remap.dmp tables=SCOTT.EMP
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."EMP" 8.554 KB 14 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "RMAN"."SYS_IMPORT_TABLE_01" successfully completed at 02:00:34
Running the query again against the table yielded the following.
SQL> select * from SCOTT.EMP;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO--------- ---------- --------- ----- --------- ------- ----- -------7369 SMITH CLERK 7902 17-DEC-80 5000 207499 ALLEN SALESMAN 7698 20-FEB-81 5000 300 307521 WARD SALESMAN 7698 22-FEB-81 5000 500 307566 JONES MANAGER 7839 02-APR-81 5000 207654 MARTIN SALESMAN 7698 28-SEP-81 5000 1400 307698 BLAKE MANAGER 7839 01-MAY-81 5000 307782 CLARK MANAGER 7839 09-JUN-81 5000 107788 SCOTT ANALYST 7566 19-APR-87 5000 207839 KING PRESIDENT 17-NOV-81 5000 107844 TURNER SALESMAN 7698 08-SEP-81 5000 0 307876 ADAMS CLERK 7788 23-MAY-87 5000 207900 JAMES CLERK 7698 03-DEC-81 5000 307902 FORD ANALYST 7566 03-DEC-81 5000 207934 MILLER CLERK 7782 23-JAN-82 5000 10
14 rows selected.
Elapsed: 00:00:00.00
As you can see the SAL column has now a constant value, just what we wanted.
The data remapping is an excellent option to make your sensitive information protected when you need to provide dump files from your production database to other folks. If you want to pass the exported dump file to developers for tables which have credit card or social security numbers the sensitive values can be remapped easily. You can use a function to generate random arbitrary numbers instead of the actual credit card or SSN numbers while taking the export. The original data in your production database requires no change and stays as it is.
No comments:
Post a Comment