Breadcrumbs

Encrypting an Existing Tablespace with Online Conversion


To encrypt an existing tablespace with online conversion, use ALTER TABLESPACE with the ONLINE and ENCRYPT clauses.

  1. Log in to the DB instance as a system user.

SQL
SQL> connect system/<password>;

  1. Open the HSM Keystore.

SQL
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY <hsm_password>;

  1. Create a tablespace.

SQL
SQL> create tablespace NONSECURE_TS DATAFILE 'C:/Oraclenew/oradata/ORCLNEW/NONSECURETS_01.DBF' SIZE 10M;

  1. Create EMP table inside the NONSECURE_TS tablespace.

SQL
SQL> create table EMP (EMPID NUMBER(4),NAME VARCHAR(100),SALARY NUMBER(6)) tablespace NONSECURE_TS;

  1. Add data to the EMP table.

SQL
SQL> insert into EMP values (0001, 'Michael Jackson', 999999); 
SQL> insert into EMP values (0002, 'Lady Gaga', 888888); 
SQL> insert into EMP values (0003, 'Freddie Mercury', 777777); 
SQL> insert into EMP values (0004, 'Steven Tyler', 666666); 
SQL> commit;

  1. View the data from the EMP table.

SQL
SQL> select * from EMP;

  1. Check that the COMPATIBLE parameter is set correctly according to the DB version.

SQL
SQL> show PARAMETER COMPATIBLE; 
Example 
SQL> show PARAMETER COMPATIBLE; 
NAME TYPE VALUE 
------------ ----------- ------ 
compatible string 23.6.0

  1. Encrypt the NONSECURE_TS tablespace.

SQL
SQL> alter tablespace NONSECURE_TS ENCRYPTION ONLINE using 'AES192' ENCRYPT FILE_NAME_CONVERT = ('NONSECURETS_01.DBF','SECURETS_02.DBF');

  1. View data from the EMP table.

SQL
SQL> select * from EMP;

  1. Verify the NONSECURE_TS tablespace got encrypted.

SQL
SQL> Select TABLESPACE_NAME, ENCRYPTED FROM DBA_TABLESPACES;

  1. Close the keystore.

SQL
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY <hsm_password>;

Now, try to view the contents of the EMP table; nothing is displayed as the wallet is closed.

As the keystore is closed, you will get an error message “ORA-28365: wallet is not open” and hence you cannot view the data from the EMP table.

  1. Open the keystore.

SQL
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY <hsm_password>;

  1. Verify the EMP table.

SQL
SQL> select * from EMP;