To decrypt a table, move the table out of the encrypted tablespace.
Background information
Assume the encryption status of table t1 is as follows, and table t1 is located in the encrypted tablespace sectest_ts1.
obclient> SELECT * FROM oceanbase.V$OB_ENCRYPTED_TABLES;
+----------+------------+---------------+---------------+-----------+----------------------------------+-------------+------------------+------------------+--------+--------+
| TABLE_ID | TABLE_NAME | TABLESPACE_ID | ENCRYPTIONALG | ENCRYPTED | ENCRYPTEDKEY | MASTERKEYID | BLOCKS_ENCRYPTED | BLOCKS_DECRYPTED | STATUS | CON_ID |
+----------+------------+---------------+---------------+-----------+----------------------------------+-------------+------------------+------------------+--------+--------+
| 500010 | t1 | 500009 | aes-256 | YES | xxxxxxxxxxxxxxxxxxxxxxxxxxxx7882 | xxxx08 | 0 | 0 | NORMAL | 0 |
+----------+------------+---------------+---------------+-----------+----------------------------------+-------------+------------------+------------------+--------+--------+
1 row in set
obclient> SHOW CREATE TABLE t1;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`id1` int(11) DEFAULT NULL,
`id2` int(11) DEFAULT NULL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 TABLESPACE `sectest_ts1` |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set
Move the table out of the encrypted tablespace
Log in to the database as the administrator.
Perform a progressive major compaction on the table.
Set the value of
progressive_merge_numto a number greater than1and execute theOPTIMIZEcommand to prepare for the progressive major compaction.Here is an example:
obclient> ALTER TABLE t1 SET progressive_merge_num = 3; obclient> OPTIMIZE TABLE t1;Manually initiate multiple rounds of progressive major compaction.
The statement to initiate one round of progressive major compaction is as follows:
obclient> ALTER SYSTEM MAJOR FREEZE;Note
Decryption is an asynchronous process. The decryption process requires a progressive major compaction on the table to ensure all data is decrypted.
Create an unencrypted tablespace
ts2.obclient> CREATE TABLESPACE ts2;Connect to the database where the table is located.
Move table
t1from the encrypted tablespacesectest_ts1to the unencrypted tablespacets2.obclient> ALTER TABLE t1 TABLESPACE ts2;After the operation succeeds, execute the
SHOW CREATE TABLEstatement, which shows that tablet1has been moved to tablespacets2.obclient> SHOW CREATE TABLE t1; +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | t2 | CREATE TABLE `t1` ( `id1` int(11) DEFAULT NULL, `id2` int(11) DEFAULT NULL ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 TABLESPACE `ts2` | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in setQuery the
oceanbase.V$OB_ENCRYPTED_TABLESview to confirm the encryption status of the table.obclient> SELECT * FROM oceanbase.V$OB_ENCRYPTED_TABLES; +------------------+------------+------------------+---------------+-----------+--------------+-------------+------------------+------------------+------------+--------+ | TABLE_ID | TABLE_NAME | TABLESPACE_ID | ENCRYPTIONALG | ENCRYPTED | ENCRYPTEDKEY | MASTERKEYID | BLOCKS_ENCRYPTED | BLOCKS_DECRYPTED | STATUS | CON_ID | +------------------+------------+------------------+---------------+-----------+--------------+-------------+------------------+------------------+------------+--------+ | 500010 | t1 | 500015 | | NO | | 0 | 2 | 0 | NORMAL | 0 | +------------------+------------+------------------+---------------+-----------+--------------+-------------+------------------+------------------+------------+--------+ 1 row in setYou can see that the
ENCRYPTEDfield of tablet1isNO, indicating that the decryption operation for tablet1was successful.