Decrypt a table

To decrypt a table, you need only to remove the table from the encrypted tablespace.

Background

Assume that table t1 is in the following encryption status and is stored in an encrypted tablespace sectest_ts1.

obclient> SELECT * FROM V$OB_ENCRYPTED_TABLES;
+----------+------------+---------------+---------------+-----------+----------------------------------+-------------+------------------+------------------+--------+--------+
| TABLE_ID | TABLE_NAME | TABLESPACE_ID | ENCRYPTIONALG | ENCRYPTED | ENCRYPTEDKEY                     | MASTERKEYID | BLOCKS_ENCRYPTED | BLOCKS_DECRYPTED | STATUS | CON_ID |
+----------+------------+---------------+---------------+-----------+----------------------------------+-------------+------------------+------------------+--------+--------+
|   500005 | T1         |        500004 | aes-256       | YES       | xxxxxxxxxxxxxxxxxxxxxxxxxxxx66F3 |      xxxx03 |                0 |                0 | NORMAL |   1010 |
+----------+------------+---------------+---------------+-----------+----------------------------------+-------------+------------------+------------------+--------+--------+
1 row in set

obclient> SHOW CREATE TABLE t1;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE | CREATE TABLE
     |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| T1    | CREATE TABLE "T1" (
  "ID1" NUMBER(38),
  "ID2" NUMBER(38)
) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0  TABLESPACE "SECTEST_TS1" |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

Remove the table from the encrypted tablespace

1. Log on to an Oracle tenant of a cluster as an administrator.

2. Perform a progressive compaction on the table.

   1. Set the progressive_merge_num parameter to a value greater than 1, and perform the SHRINK SPACE operation.

      Here is an example:

      obclient> ALTER TABLE t1 SET progressive_merge_num = 3;       
      obclient> ALTER TABLE t1 SHRINK SPACE;
      

   2. Manually initiate multiple progressive compactions.

      Execute the following statement to initiate a progressive compaction:

      obclient> ALTER SYSTEM MAJOR FREEZE;
      

   Note:

   Decryption is an asynchronous process. Progressive compactions must be performed on the table to ensure that all data is decrypted.

3. Create an unencrypted tablespace ts2.

   obclient> CREATE TABLESPACE ts2;
   

4. Move the t1 table from the encrypted tablespace sectest_ts1 to the unencrypted tablespace ts2.

   obclient> ALTER TABLE t1 TABLESPACE ts2;
   

   Then, execute the SHOW CREATE TABLE statement to check whether the t1 table is moved to the tablespace ts2:

   obclient> SHOW CREATE TABLE t1;
   +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | TABLE | CREATE TABLE                                                                                                                                                                                        |
   +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | T1    | CREATE TABLE "T1" (
     "ID1" NUMBER(38),
     "ID2" NUMBER(38)
   ) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0  TABLESPACE "TS2" |
   +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   1 row in set
   

5. Query the V$OB_ENCRYPTED_TABLES view to check the encryption status of the table.

   obclient>  SELECT * FROM V$OB_ENCRYPTED_TABLES;
   +------------------+------------+------------------+---------------+-----------+--------------+-------------+------------------+------------------+------------+--------+
   | TABLE_ID         | TABLE_NAME | TABLESPACE_ID    | ENCRYPTIONALG | ENCRYPTED | ENCRYPTEDKEY | MASTERKEYID | BLOCKS_ENCRYPTED | BLOCKS_DECRYPTED | STATUS     | CON_ID |
   +------------------+------------+------------------+---------------+-----------+--------------+-------------+------------------+------------------+------------+--------+
   | 500005           | T1         | 500012           |               | NO        |              |           0 |                2 |                0 | NORMAL     |   1010 |
   +------------------+------------+------------------+---------------+-----------+--------------+-------------+------------------+------------------+------------+--------+
   1 row in set
   

   It is clear that the ENCRYPTED column of table t1 is NO. This indicates that table t1 is decrypted.

References

• Enable storage encryption for a new table
• Enable storage encryption for an existing table