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;