Decrypt a table

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

Background information

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

obclient> SELECT * FROM v$encrypted_tables;
+------------------+------------+------------------+---------------+-----------+----------------------------------+------------------+------------------+------------------+--------+
| TABLE_ID         | TABLE_NAME | TABLESPACE_ID    | ENCRYPTIONALG | ENCRYPTED | ENCRYPTEDKEY                     | MASTERKEYID      | BLOCKS_ENCRYPTED | BLOCKS_DECRYPTED | STATUS |
+------------------+------------+------------------+---------------+-----------+----------------------------------+------------------+------------------+------------------+--------+
| 1103909674337105 |   T1       | 1103909674288105 | aes-256       | YES       | 16E9DE76E8A3B87C58DAC6E2F1C69953 | 1103909674288105 |                3 |                0 | NORMAL |
+------------------+------------+------------------+---------------+-----------+----------------------------------+------------------+------------------+------------------+--------+
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 the database as the administrator.

2. Create an unencrypted tablespace ts2.

   obclient> CREATE TABLESPACE ts2;
   

3. 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
   

4. Query the sys.v$encrypted_tables view to check the encryption status of the table.

   obclient>  SELECT * FROM sys.v$encrypted_tables;
   +------------------+------------+------------------+---------------+-----------+--------------+-------------+------------------+------------------+------------+
   | TABLE_ID         | TABLE_NAME | TABLESPACE_ID    | ENCRYPTIONALG | ENCRYPTED | ENCRYPTEDKEY | MASTERKEYID | BLOCKS_ENCRYPTED | BLOCKS_DECRYPTED | STATUS     |
   +------------------+------------+------------------+---------------+-----------+--------------+-------------+------------------+------------------+------------+
   | 1103909674337105 | T1         | 1103909674288105 |               | NO        |              |           0 |                2 |                0 | NORMAL     |
   +------------------+------------+------------------+---------------+-----------+--------------+-------------+------------------+------------------+------------+
   1 row in set (0.03 sec)
   

   It is clear that the STATUS field of the t1 table is NORMAL, which means that the t1 table is unencrypted.