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_ENTRY
INTERIM TABLE_NAME – > EMPLOYEE_ENTRY_INT

USERNAME            -> DATATS
NEW TABLESPACE. – > DATATS_ENC

1. Create an encrypted tablespaces:

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. Create a interim table by using the structure of the original table:

The original table is a partitioned table with interval partition. So i am using exdp and impdp to generate the proper DDL file.

expdp dumpfile=wm.dmp logfile=wm.log directory=T tables=DATATS.EMPLOYEE_ENTRY content=metadata_only

impdp dumpfile=wm.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

3. check the partition count in both the original and interim table:

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

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

172

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

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

172

4. Check whether redef is posible or not:

SQL> SET SERVEROUTPUT ON

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

PL/SQL procedure successfully completed.

5. Now start the redef process:

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_ENTRY',
                      int_table => 'EMPLOYEE_ENTRY_INT',
                      options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/

PL/SQL procedure successfully completed.

Elapsed: 00:10:53.12

6. Drop the not null constrains from the interim table ( if any):

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


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

Table altered.

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

Table altered.

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

Table altered.

7. Copy the dependents:

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_ENTRY', 'EMPLOYEE_ENTRY_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_ENTRY', 'EMPLOYEE_ENTRY_INT');
END;
/ 
PL/SQL procedure successfully completed.

9. Finish the redef process:

SQL> exec DBMS_REDEFINITION.FINISH_REDEF_TABLE('DATATS','EMPLOYEE_ENTRY','EMPLOYEE_ENTRY_INT');

PL/SQL procedure successfully completed.