转载

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_varchar

Export: Release 11.2.0.4.0 - Production on Fri Mar 25 11:02:51 2016

Copyright (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_impdp

Import: Release 11.2.0.4.0 - Production on Fri Mar 25 11:09:20 2016

Copyright (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_impdp

Import: Release 11.2.0.4.0 - Production on Fri Mar 25 11:11:23 2016

Copyright (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
*/

正文到此结束