SQL> -- Remove existing synthetic data
3 M6 G$ r0 X# w! s- c2 X SQL> TRUNCATE TABLE app_001.transactions;
, y5 r" T, O+ b3 _1 y Table truncated.. P+ `. s4 E7 N! _+ y0 l3 T
SQL> -- Disable encryption of the credit card column8 o6 Q+ p \$ D) _8 n& P
SQL> ALTER TABLE app_001.transactions
/ G" Z, y: J! ~6 v 2 MODIFY (credit_card DECRYPT);
5 J, @: y! [6 V9 \. ? Table altered." V' d+ ~* \ L/ D! _7 j0 K
SQL> -- Load new synthetic data
, A( x7 W2 y) r) m. P3 i; p) }0 p SQL> BEGIN
$ E0 _+ ^* O& `6 E, c2 x 2 -- AMEX7 |+ `" G5 C% o x3 g1 C
3 FOR i IN 1 .. 100000 LOOP
' W# r- m. W( u0 i) H% S 4 INSERT INTO app_001.transactions(trans_id, credit_card)
# g* H x# N( t 5 VALUES (. O) N7 |2 d9 r2 R5 S
6 i,* t/ S8 M/ z+ w9 l
7 ’34’ || TRUNC(DBMS_RANDOM.VALUE(low=>0, high=>99999999999999))
# R* g* g/ O$ L! r, s* H 8 );
8 X. d D7 h! Q8 ?" ] 9 END LOOP;
8 n1 F! m. B2 e9 r; o: i+ r! T 10 COMMIT;$ f+ [ R: G4 t; H2 q" f
11 -- VISA, n I' D C* s: A
12 FOR i IN 100001 .. 400000 LOOP
5 `9 a% ]) Y8 }. D6 B 13 INSERT INTO app_001.transactions(trans_id, credit_card)% U) Z( d; g( J e! o
14 VALUES (# R: `, v" h0 p! \- X9 V2 s
15 i,
( w& ]; }9 M" j; B; c$ w# g1 s 16 ’4’ || TRUNC(DBMS_RANDOM.VALUE(low=>0, high=>999999999999999)), |, W' R2 o- P9 F
17 );: R3 q$ a" W% R' ?7 p3 T
18 END LOOP;
7 ?3 y7 N7 Z& ~* D5 X0 G 19 COMMIT;+ ?- v; A8 z4 f) a6 o9 N( p
20 -- MASTERCARD
- ~! P, Q& ~4 F; k# ]7 H 21 FOR i IN 400001 .. 500000 LOOP; B% R. ^- b+ F0 b, a/ a
22 INSERT INTO app_001.transactions(trans_id, credit_card)
$ H" r0 z, A& x1 r H 23 VALUES (, ^7 _5 g! s& `1 E- e! x
24 i,: l4 t, g3 P9 a. \3 `$ Q; o
25 ’54’ || TRUNC(DBMS_RANDOM.VALUE(low=>0, high=>99999999999999))
& e( Y' U; {" V( V- } 26 );8 `/ J Y% p+ u2 {# } ?& C6 i
27 END LOOP;
# I$ Z8 a# k7 E3 s6 k$ y 28 COMMIT;
0 t" Z6 k( x1 ^% G 29 END;
' b' o0 s6 r: {/ h9 Z& l* E 30 /" H" a% a& e2 ~
PL/SQL procedure successfully completed.
5 q3 U4 B# \: G% y/ f1 M9 a SQL> -- Time how long it takes to encrypt credit card data
o* D# ^4 I2 N- U SQL> -- with corresponding indexes in place
. r+ m- M) I$ h$ V SQL> SET TIMING ON;
9 i$ V# f7 n, O- i- l6 T5 { F SQL> ALTER TABLE app_001.transactions
) {) N# W+ O' ~' F( _# D& Q; ? 2 MODIFY (credit_card ENCRYPT NO SALT);! o9 o1 a g6 v$ \) h1 f
Table altered.9 [* R! f' _# r3 |' I
Elapsed: 00:02:27.18
1 Y: v! O6 f5 y& V9 N% k/ `) I SQL> SET TIMING OFF;+ w, M6 j3 c% s# z/ e
SQL> -- Remove existing synthetic data
% I: \2 q, u* p7 E- e+ t SQL> TRUNCATE TABLE app_001.transactions;" T! L. W" U+ ?& ?8 U7 l: R
Table truncated.3 C& R# u8 j- }! @% R3 K& q
SQL> -- Drop all indexes that correspond to the credit card column
, h% z9 O" b! U" }/ [) H# ? SQL> DROP INDEX app_001.transactions_ndx1;
# ~' s! q1 l3 V+ @# J* l, _+ c Index dropped.; E7 B! L/ E% h4 J' M- z% ?$ m3 i5 ?6 W
SQL> -- Disable encryption of the credit card column! F# ~* q7 j) b/ f" D
SQL> ALTER TABLE app_001.transactions
0 B* K, D1 G, J" g/ y) L3 A
: N+ w6 G' g* S( o! [+ N 2 MODIFY (credit_card DECRYPT); |