Breadcrumbs

Encrypting an Existing Tablespace with Offline Conversion

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

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

SQL
SQL> connect system/<password>;

  1. Open HSM Keystore.

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

  1. Create a tablespace.

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

  1. Create an EMP table inside the NONSECURE01_TS tablespace.

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

  1. Insert data into 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 data from the EMP table.

SQL
SQL> select * from EMP;

  1. Bring the NONSECURE01_TS tablespace offline.

SQL
SQL> alter tablespace NONSECURE01_TS OFFLINE NORMAL;

  1. Encrypt the NONSECURE01_TS tablespace.

SQL
SQL> alter tablespace NONSECURE01_TS ENCRYPTION OFFLINE ENCRYPT;

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

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

  1. Bring the tablespace online.

SQL
SQL> alter tablespace NONSECURE01_TS ONLINE;

  1. Close the keystore.

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

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

SQL
SQL> select * from EMP;