Oracle通过expdp的remap_data实现简单的数据脱敏

Oracle 11g开始,在impdp/expdp中提供remap_data参数,实现对导入或导出操作时转换数据.如果生产库数据要导出给测试库使用,并需要转换敏感数据,可以考虑通过remap_data参数去实现.以下是简单的单表示例:
oracle version:11.2.0.4[oracle@ct6605 ~]$ ORACLE_SID=ct66
#建测试表
SQL> create table scott.t_expdp as select * from dba_objects;
#建导入导出目录
SQL> create or replace directory home_dump as "/home/oracle";
#建用于转换数据的函数包
#转换的逻辑和复杂度可根据需求确定
SQL> create or replace package scott.pkg_remap
is
#转换number类型
function f_remap_number（p_number number） return number;
#转换varchar类型
function f_remap_varchar（p_varchar varchar2） return varchar2;
end;
/SQL> create or replace package body scott.pkg_remap
2 is
function f_remap_number（p_number number） return number as
3 4 begin
5 return floor（dbms_random.value（1, 100000））;
6 end;
function f_remap_varchar（p_varchar varchar2） return varchar2 as
7 8 begin
9 return dbms_random.string（"A",10）;
10 end;
11 end;
12 /SQL> exit#使用expdp时remap_data参数导出时转换数据
#remap_data格式是要转换的一个字段对应一个转换函数
#reuse_dumpfiles也是11g后的参数,用以确定是否覆盖同名导出文件
[oracle@ct6605 ~]$ expdp system dumpfile=home_dump:remap_t_expdp.dmp tables=scott.t_expdp reuse_dumpfiles=y remap_data=scott.t_expdp.object_id:scott.pkg_remap.f_remap_number,scott.t_expdp.object_name:scott.pkg_remap.f_remap_varcharExport: Release 11.2.0.4.0 - Production on Fri Mar 25 11:02:51 2016Copyright （c） 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Password:Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** dumpfile=home_dump:remap_t_expdp.dmp tables=scott.t_expdp reuse_dumpfiles=y remap_data=scott.t_expdp.object_id:scott.pkg_remap.f_remap_number,scott.t_expdp.object_name:scott.pkg_remap.f_remap_varchar
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 10 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SCOTT"."T_EXPDP" 7.257 MB 86526 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/home/oracle/remap_t_expdp.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Fri Mar 25 11:03:08 2016 elapsed 0 00:00:13#使用impdp导出转换后的表,这里由于测试,所以导入到同库同用户下
#remap_table也是11g后的参数,用以重映射表
[oracle@ct6605 ~]$ impdp system dumpfile=home_dump:remap_t_expdp.dmp remap_table=scott.t_expdp:t_impdpImport: Release 11.2.0.4.0 - Production on Fri Mar 25 11:09:20 2016Copyright （c） 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Password: UDI-00001: user requested cancel of current operation[oracle@ct6605 ~]$ impdp system dumpfile=home_dump:remap_t_expdp.dmp remap_table=scott.t_expdp:t_impdpImport: Release 11.2.0.4.0 - Production on Fri Mar 25 11:11:23 2016Copyright （c） 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Password:Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** dumpfile=home_dump:remap_t_expdp.dmp remap_table=scott.t_expdp:t_impdp
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."T_IMPDP" 7.257 MB 86526 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Fri Mar 25 11:11:28 2016 elapsed 0 00:00:02
[oracle@ct6605 ~]$ sqlplus / as sysdba
#查看导入的数据是否经过转换
SQL> select object_name,object_id from scott.t_impdp where rownum<10;
/*
OBJECT_NAME OBJECT_ID
swecninjYb 34242
axIpkMKaJw 96259
DpBWmPGhyo 80463
eXcEWFyDvL 46759
uZJIPkYruN 23656
uSRsuPlXNG 78736
CBeviPFlhr 44909
NyQHLHBvKi 16672
vPWTIdMJkN 50456
*/更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址