利用数据泵屏幕敏感数据！

How to Mask the confidential or sensitive data with remap_data in datapump

List the test steps as below:

SQL> conn system/system@orcl

Connected.

SQL> create user henry identified by henry;

 User created.

 SQL> grant dba to henry;

 Grant succeeded.

 SQL> create user bai identified by bai;

 User created.

 SQL> grant dba to bai;

 Grant succeeded.

 SQL> conn henry/henry@orcl

Connected.

SQL>  create table test (id number,name varchar2(10));

 Table created.

 SQL> insert into test values(1,'Baiwentao');

 1 row created.

 SQL> insert into test values(2,'Henrybai');

 1 row created.

 SQL> commit;

 Commit complete.

 SQL> conn system/system@orcl

Connected.

SQL> grant read,write on directory dmpdir2 to henry,bai;

 Grant succeeded.

 SQL>conn henry/henry@orcl

SQL> create package pkg_obfuscate

  2      as

  3     function mask_name(p_in varchar2) return varchar2;

  4      end;

  5      /

 Package created.

 SQL>

SQL>     create or replace package body pkg_obfuscate

  2      as

  3      function mask_name(p_in varchar2)

  4      return varchar2

  5      is

  6      begin

  7      return dbms_random.string('a',8);

  8      end;

  9     end;

 10  /

 Package body created.

 SQL>exit

D:\>expdp henry/henry@orcl tables=test dumpfile=test.dmp directory=dmpdir2 remap_data=henry.TEST.NAME:henry.pkg_obfuscate.mask_name

 Export: Release 11.2.0.1.0 - Production on Tue Nov 29 19:44:14 2011

 Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

 Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "HENRY"."SYS_EXPORT_TABLE_01":  henry/********@orcl tables=test dumpfil

e=test.dmp directory=dmpdir2 remap_data=henry.TEST.NAME:henry.pkg_obfuscate.mask_name

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

. . exported "HENRY"."TEST"                              5.429 KB       2 rows

Master table "HENRY"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

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

Dump file set for HENRY.SYS_EXPORT_TABLE_01 is:

  D:\DBDUMP\TEST.DMP

Job "HENRY"."SYS_EXPORT_TABLE_01" completed with 19 error(s) at 19:44:28

  D:\>

D:\>impdp bai/bai@orcl dumpfile=test.dmp directory=dmpdir2 remap_schema=henry:bai

 Import: Release 11.2.0.1.0 - Production on Tue Nov 29 19:46:00 2011

 Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

 Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Prodution

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "BAI"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "BAI"."SYS_IMPORT_FULL_01":  bai/********@orcl dumpfile=test.dmp direc

ory=dmpdir2 remap_schema=henry:bai

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "BAI"."TEST"                                5.429 KB       2 rows

Job "BAI"."SYS_IMPORT_FULL_01" successfully completed at 19:46:05

 D:\>sqlplus bai/bai@orcl

SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 29 19:46:14 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from test;

        ID NAME
---------- ----------
         1 DGJoCrAV
         2 icvknyac