DBA Hub

📋Steps in this guide1/3

How To Encrypt A Table Using Dbm_redef With Zero Down Time

We can encrypt a table using simple alter table command, However alter table command on big tables takes time and during this process, transaction will be

oracle configurationintermediate
by OracleDba
12 views
1

Overview

We can encrypt a table using simple alter table command, However alter table command on big tables takes time and during this process, transaction will be blocked on that table. So best solution to avoid downtime is to use dbms_redef method to move the table to a new encrypted tablespace. ORIGINAL TABLE_NAME – EMPLOYEE INTERIM TABLE_NAME – EMPLOYEE_INT USERNAME            – DATATS NEW TABLESPACE. – DATATS_ENC - Create an encrypted tablespaces:

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLESPACE DATATS_ENC datafile '+DATA' size 30g encryption using 'AES256' DEFAULT STORAGE(ENCRYPT);


SQL> select b.name,a.ENCRYPTIONALG  from v$encrypted_tablespaces a,v$tablespace b where a.ts#=b.ts#;

NAME                           ENCRYPT
------------------------------ -------
DATATS_ENC                     AES256


SQL>  select TABLESPACE_NAME,ENCRYPTED from dba_tablespaces where  TABLESPACE_NAME='DATATS_ENC';

TABLESPACE_NAME                ENC
------------------------------ ---
DATATS_ENC                     YES
2

Section 2

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
expdp dumpfile=test.dmp logfile=wm.log directory=T tables=DATATS.EMPLOYEE content=metadata_only

impdp dumpfile=test.dmp logfile=wm.log directory=t sqlfile=tab.sql include=TABLE

Now open the tab.sql file and change the tablespace_name to DATATS_ENC .

SQL>@tab.sql

Table created

SQL>select count(*) from dba_tab_partitions where table_owner='DATATS' and TABLE_NAME='EMPLOYEE';

COUNT(*)
---------

172

SQL>select count(*) from dba_tab_partitions where table_owner='DATATS' and TABLE_NAME='EMPLOYEE_INT';

COUNT(*)
---------

172

SQL> SET SERVEROUTPUT ON

BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('DATATS','EMPLOYEE', DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/SQL> SQL>   2    3    4

PL/SQL procedure successfully completed.

You can use parallel for big tables for faster redef process.
ALTER SESSION FORCE PARALLEL DML PARALLEL 8;
ALTER SESSION FORCE PARALLEL QUERY PARALLEL 8;


BEGIN
   DBMS_REDEFINITION.START_REDEF_TABLE(
                      uname => 'DATATS',
                      orig_table => 'EMPLOYEE',
                      int_table => 'EMPLOYEE_INT',
                      options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/

PL/SQL procedure successfully completed.

Elapsed: 00:10:53.12

SQL>  select *  from dba_constraints where owner='DATATS' and table_name='EMPLOYEE';


SQL> alter table  "DATATS"."EMPLOYEE_INT" drop constraint SYS_C0013751;

Table altered.

SQL> alter table  "DATATS"."EMPLOYEE_INT" drop constraint SYS_C0013752;

Table altered.

SQL>  alter table  "DATATS"."EMPLOYEE_INT" drop constraint SYS_C0013750;

Table altered.

ALTER SESSION FORCE PARALLEL DML PARALLEL 16;
 ALTER SESSION FORCE PARALLEL QUERY PARALLEL 16;


DECLARE
   error_count pls_integer := 0;
BEGIN
    DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('DATATS', 'EMPLOYEE', 'EMPLOYEE_INT', dbms_redefinition.cons_orig_params, TRUE,TRUE,TRUE,FALSE, error_count);
    DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count));
END;
/

Session altered.

SQL>
Session altered.

SQL> SQL> SQL> 2 3 4 5 6 7


PL/SQL procedure successfully completed
8.Now do the final sync:

SQL> BEGIN
   DBMS_REDEFINITION.SYNC_INTERIM_TABLE('DATATS', 'EMPLOYEE', 'EMPLOYEE_INT');
END;
/ 
PL/SQL procedure successfully completed.
3

Section 3

Code/Command (click line numbers to comment):

1
2
3
SQL> exec DBMS_REDEFINITION.FINISH_REDEF_TABLE('DATATS','EMPLOYEE','EMPLOYEE_INT');

PL/SQL procedure successfully completed.

Comments (0)

Please to add comments

No comments yet. Be the first to comment!