- 09 Oct, 2023
- 0 Comments
- 3 Mins Read
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 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:
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 AES256SQL> 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.
1 2 3 4 5 6 7 8 9 | expdp dumpfile=test.dmp logfile=wm.log directory=T tables=DATATS.EMPLOYEE content=metadata_onlyimpdp dumpfile=test.dmp logfile=wm.log directory=t sqlfile=tab.sql include=TABLENow open the tab.sql file and change the tablespace_name to DATATS_ENC .SQL>@tab.sqlTable created |
3. check the partition count in both the original and interim table:
1 2 3 4 5 6 7 8 9 10 11 12 13 | SQL>select count(*) from dba_tab_partitions where table_owner='DATATS' and TABLE_NAME='EMPLOYEE';COUNT(*)---------172SQL>select count(*) from dba_tab_partitions where table_owner='DATATS' and TABLE_NAME='EMPLOYEE_INT';COUNT(*)---------172 |
4. Check whether redef is posible or not:
1 2 3 4 5 6 7 8 | SQL> SET SERVEROUTPUT ONBEGINDBMS_REDEFINITION.CAN_REDEF_TABLE('DATATS','EMPLOYEE', DBMS_REDEFINITION.CONS_USE_ROWID);END;/SQL> SQL> 2 3 4PL/SQL procedure successfully completed. |
5. Now start the redef process:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | 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 |
6. Drop the not null constraints from the interim table ( if any):
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | 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. |
7. Copy the dependents:
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 | 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 7PL/SQL procedure successfully completed8.Now do the final sync:SQL> BEGIN DBMS_REDEFINITION.SYNC_INTERIM_TABLE('DATATS', 'EMPLOYEE', 'EMPLOYEE_INT');END;/ PL/SQL procedure successfully completed. |
8. Finish the redef process:
1 2 3 | SQL> exec DBMS_REDEFINITION.FINISH_REDEF_TABLE('DATATS','EMPLOYEE','EMPLOYEE_INT');PL/SQL procedure successfully completed. |