解除加密

将表从加密的表空间中移出即可解除表的加密。

背景信息

假设表 t1 的加密状态如下，且表 t1 在加密的表空间 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

将表从加密表空间中移出

1. 管理员登录到集群的 Oracle 租户。

2. 对表执行渐进合并。

   a. 将 progressive_merge_num 的值设置为大于 1 的数，并执行 SHRINK SPACE 操作，准备做渐进合并。

   示例如下：

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

   b. 手动发起多轮渐进合并。

   发起一轮渐进合并的语句如下：

   obclient> ALTER SYSTEM MAJOR FREEZE;
   

   说明

   解除加密是异步过程，解密过程需要对表进行渐进合并保证所有数据解密。

3. 创建未加密的表空间 ts2。

   obclient> CREATE TABLESPACE ts2;
   

4. 将表 t1 从加密的表空间 sectest_ts1 中移到未加密的表空间 ts2 中。

   obclient> ALTER TABLE t1 TABLESPACE ts2;
   

   操作成功后，执行 SHOW CREATE TABLE 语句，可以看到表 t1 已经移到了表空间 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. 查询 V$OB_ENCRYPTED_TABLES 视图，确认表的加密状态。

   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
   

   可以看到表 t1 的 ENCRYPTED 字段为 NO，说明表 t1 解除加密操作成功。

相关文档

• 为新创建的表设置存储加密
• 为已有表设置存储加密