Data masking

SQL> select ename,sal from emp;

ENAME SAL
---------- ----------
SMITH 800
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500
ADAMS 1100

ENAME SAL
---------- ----------
JAMES 950
FORD 3000
MILLER 1300

已选择14行。

Then start to do data mask:

After job running.

Seee the result.

SQL> /

ENAME SAL
---------- ----------
SMITH 7707
ALLEN 6301
WARD 7109
JONES 6900
MARTIN 7109
TURNER 7604
JAMES 6302
BLAKE 7410
CLARK 6608
KING 6205
SCOTT 7403

ENAME SAL
---------- ----------
ADAMS 6211
FORD 7403
MILLER 6206

this help us hide the confidential data when clone or copy database to test or development.

Actually,the EM help us do the following things:

-- Target database: orcl.oracle.com
-- Script generated at: 05-JAN-2011 11:34
COMMIT
ALTER SESSION ENABLE PARALLEL DML
DROP TABLE "MGMT_DM_TT_7" PURGE
declare
adj number:=0;
num number:=0;
begin
select length(count(*)) into adj from (select distinct "SAL" from "SCOTT"."EMP");
num := adj;
adj := greatest(adj - 2, 0);
execute immediate 'create table MGMT_DM_TT_7
(orig_val null, new_val null) NOLOGGING PARALLEL as
select CAST(null AS NUMBER(7, 2)) orig_val, CAST(null AS NUMBER(7, 2)) new_val from dual union all
select s.orig_val,
case
when s.subset = 1 then
CAST(
lpad(6000 + mod(lpad(trunc(dbms_random.value(0, substr(2000, 1, 2 - least(2, greatest('||adj||' - 0, 0))))), 2 - least(2, greatest('||adj||' - 0, 0)), 0)
|| lpad(nvl(substr(s.new_num, 1, 2 + least(2, greatest('||adj||' - 0, 0))),0), 2 + least(2, greatest('||adj||' - 0, 0)), 0), 2000), 4, 0)
AS NUMBER(7, 2))
end new_val
from (select rownum rn, orig_val, subset, rid, lpad(rownum - 1, '||num||', 0) new_num
from (select "SAL" orig_val, min(rowid) rid, min(
case
when 1=1 then 1
end
) subset
from "SCOTT"."EMP" group by "SAL")) s
where 1=1 and s.orig_val is not null
';
DBMS_STATS.GATHER_TABLE_STATS(NULL, '"MGMT_DM_TT_7"', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, degree=>DBMS_STATS.DEFAULT_DEGREE);
end;

DECLARE
CURSOR fk_sql IS select refr.owner, refr.table_name, refr.constraint_name
from dba_constraints refd, dba_constraints refr
where refd.owner = 'SCOTT' and
refd.table_name = 'EMP' and
refr.constraint_type = 'R' and
refr.r_owner = refd.owner and
refr.r_constraint_name = refd.constraint_name;
BEGIN
FOR fk IN fk_sql
LOOP
EXECUTE IMMEDIATE 'ALTER TABLE "' || fk.owner || '"."' ||
fk.table_name || '" DROP CONSTRAINT "' || fk.constraint_name || '"';
END LOOP;
END;
DECLARE
CURSOR c_sql IS select owner, table_name, constraint_name,
constraint_type, generated, index_name from dba_constraints
where owner = 'SCOTT' and
table_name = 'EMP' and
constraint_type <> 'R';
BEGIN
FOR c IN c_sql
LOOP
EXECUTE IMMEDIATE 'ALTER TABLE "' || c.owner || '"."' ||
c.table_name || '" DROP CONSTRAINT "' || c.constraint_name || '"';
END LOOP;
END;
DROP INDEX "SCOTT"."PK_EMP"
ALTER TABLE "SCOTT"."EMP" RENAME TO "EMP$DMASK"
CREATE TABLE "SCOTT"."EMP" TABLESPACE "USERS" PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) NOLOGGING PARALLEL AS SELECT s."EMPNO", s."ENAME", s."JOB", s."MGR", s."HIREDATE", c0m7.NEW_VAL "SAL", s."COMM", s."DEPTNO" FROM "SCOTT"."EMP$DMASK" s , MGMT_DM_TT_7 c0m7 WHERE sys_op_map_nonnull(s."SAL") = sys_op_map_nonnull(c0m7.ORIG_VAL)
ALTER TABLE "SCOTT"."EMP" LOGGING NOPARALLEL
DROP TABLE "SCOTT"."EMP$DMASK" PURGE
CREATE UNIQUE INDEX "SCOTT"."PK_EMP" ON "SCOTT"."EMP" ("EMPNO") TABLESPACE "USERS" PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) LOGGING

ALTER TABLE "SCOTT"."EMP" ADD (CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO") )
ALTER TABLE "SCOTT"."EMP" ADD (CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO") REFERENCES "SCOTT"."DEPT" ("DEPTNO") )
ALTER MATERIALIZED VIEW "SCOTT"."MV1" COMPILE
BEGIN DBMS_STATS.GATHER_TABLE_STATS('"SCOTT"', '"EMP"', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, degree=>DBMS_STATS.DEFAULT_DEGREE, cascade=>TRUE); END;