Breadcrumbs

Encrypting an Existing Tablespace with Offline Conversion

You can encrypt a data file of an existing tablespace when the tablespace is offline.

  1. Login to DB instance as a system user.

›_ sqlplus console 

SQL> connect system/<password>;

  1. Open the HSM Keystore.

›_ sqlplus console 

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

  1. Create a tablespace.

›_ sqlplus console 

SQL> create tablespace NONSECURE01_TS DATAFILE '/u01/app/oracle/admin/utimacodb/NONSECURETS_01.DBF' SIZE 10M;

  1. Create EMP table inside the NONSECURE01_TS tablespace.

›_ sqlplus console 

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

  1. Insert data into EMP table.

›_ sqlplus console 

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 EMP table.

›_ sqlplus console 

SQL> select * from EMP;

  1. Bring the NONSECURE01_TS tablespace offline.

›_ sqlplus console 

SQL> alter tablespace NONSECURE01_TS OFFLINE NORMAL;

  1. Encrypt the NONSECURE01_TS tablespace.

›_ sqlplus console 

SQL> alter tablespace NONSECURE01_TS ENCRYPTION OFFLINE ENCRYPT;

Alternatively, to encrypt individual data files within a tablespace, use the ALTER DATABASE DATAFILE SQL statement.

›_ sqlplus console 

SQL> alter database DATAFILE ' NONSECURETS01_TS.DBF' ENCRYPT;

  1. Bring the tablespace online.

›_ sqlplus console

 
SQL> alter tablespace NONSECURE01_TS ONLINE;

  1. Close the keystore.

›_ sqlplus console 

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

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

›_ sqlplus console 

SQL> select * from EMP;