Document Type | Technical Knowledge
Category | Administration
Applicable Product Version | 7FS02
Document Number | TADTI167
Overview
This explains the differences when checking column encryption and tablespace encryption.
Method
Column Encryption Method
Column encryption is handled by the worker thread during encryption processing.
The worker thread encrypts the data and then writes it to the data block in the TSM's buffer cache. Therefore, when performing a block dump, you can see the data in an encrypted state as the string as follows.
Table Creation and Block Dump
SQL> CREATE TABLE TIBERO.T2 (c1 number, c2 char(2000));
SQL> insert into tibero.t2 values(111,'A');
SQL> insert into tibero.t2 values(222,'B');
SQL> insert into tibero.t2 values(333,'C');
SQL> insert into tibero.t2 values(444,'D');
SQL> insert into tibero.t2 values(555,'E');
SQL> insert into tibero.t2 values(666,'F');
--Data query
SQL> select C1, SUBSTR(C2,1,1) from tibero.t2;
C1 SUBSTR(C2,1,1)
---------- --------------
111 A
222 B
333 C
444 D
555 E
666 F
6 rows selected.
--Query file and block numbers
SQL> select C1,
rowid,
dbms_rowid.rowid_block_number(rowid) as blkno, dbms_rowid.rowid_to_relative_fno(rowid) as fno2
from tibero.t2;
C1 ROWID BLKNO FNO2
---------- ------------------------------------------------------------------------ ---------- ----------
111 AAABA6AACAAAAE/AAA 319 2
222 AAABA6AACAAAAE/AAB 319 2
333 AAABA6AACAAAAE/AAC 319 2
444 AAABA6AACAAAAFAAAA 320 2
555 AAABA6AACAAAAFAAAB 320 2
666 AAABA6AACAAAAFAAAC 320 2
6 rows selected.
--Execute block dump (targeting the 2 blocks queried above)
SQL> alter system dump datafile 2 block 319;
System altered.
Execute Dump on the Same Block After Column Encryption
--Column encryption
SQL> ALTER TABLE TIBERO.T2 MODIFY (C2 ENCRYPT NO SALT);
Table 'TIBERO.T2' altered.
--Execute block dump (targeting the 2 blocks queried above)
SQL> alter system dump datafile 2 block 319;
System altered.
Buffer Cache Data Block Content (Before/After Column Encryption)
When performing a block dump (ALTER SYSTEM DUMP ~), you can see both the data in an unencrypted state and the data in an encrypted state as follows.
**Dump start at 2025-12-16 16:12:16
DUMP of BLOCK file #2 block #14876
**Dump start at 2025-12-16 16:12:34
DUMP of BLOCK file #2 block #319
**Dump start at 2025-12-16 16:12:34
data block Dump[dba=02_00000319(8388927),tsn=0000.000147ec,type=13,seqno =1, flag=16]
--------------------------------------------------------------
sgmt_id=4154 cleanout_tsn=0000.000147ec btxcnt=2
l1dba=02_00000311(8388919), offset_in_l1=8
btx xid undo fl tsn/credit
00 0001.30.0019 01_00021906.00018.00050 E 0000.000147eb
01 0000.00.0000 00_00000000.00000.00000 I 0000.00000000
--------------------------------------------------------------
Data block dump:
dlhdr_size=14 freespace=2060 freepos=2158 symtab_offset=0 rowcnt=3
Row piece dump:
rp 0 6178: [2010] flag=--H-FL-- itlidx=0 colcnt=2
col 0: [4]
0000: 03 C2 81 8B ....
col 1: [2003]
0000: FE 07 D0 41 20 20 20 20 20 20 20 20 20 20 20 20 ...A
0010: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
0020: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
~ ~ omitted ~ ~
**Dump start at 2017-12-18 19:10:40 DUMP of BLOCK file #2 block #14876
**Dump start at 2025-12-16 16:19:25
DUMP of BLOCK file #2 block #319
**Dump start at 2025-12-16 16:19:25
data block Dump[dba=02_00000319(8388927),tsn=0000.0001498f,type=13,seqno =1, flag=16]
--------------------------------------------------------------
sgmt_id=4154 cleanout_tsn=0000.0001498f btxcnt=2
l1dba=02_00000311(8388919), offset_in_l1=8
btx xid undo fl tsn/credit
00 0001.30.0019 01_00021906.00018.00050 E 0000.000147eb
01 0004.03.0020 01_00003746.00001.00002 E 0000.0001498d
--------------------------------------------------------------
Data block dump:
dlhdr_size=14 freespace=1952 freepos=2050 symtab_offset=0 rowcnt=3
Row piece dump:
rp 0 6142: [2046] flag=--H-FL-- itlidx=1 colcnt=2
col 0: [4]
0000: 03 C2 81 8B ....
col 1: [2039]
0000: FE 07 F4 7E B4 A9 53 C7 1C D3 77 53 ED 24 CC A2 ...~..S...wS.$..
0010: E6 59 8B 1C DA 59 EC 57 DA 51 1E 86 19 14 B8 96 .Y...Y.W.Q......
0020: D8 19 14 A0 78 EA 19 74 88 C4 B0 C6 9F 67 69 6D ....x..t.....gim
~ ~ omitted ~ ~
Tablespace Encryption Method
Write
The worker thread stores unencrypted data in the TSM (Tibero Shared Memory) buffer cache, and then the DBWR process encrypts that data and writes it to the data file.
Read
Since the data is already decrypted in the buffer cache, if the block is read directly from the buffer cache, no separate decryption processing is required.
If the block to be read does not exist in the buffer cache, the worker thread decrypts the data block while reading it from disk to the buffer cache and then stores it in the buffer cache.
Therefore, when performing a block dump (ALTER SYSTEM DUMP ~), you can see the data in an unencrypted state as follows.
--Change to normal column
SQL> ALTER TABLE TIBERO.T2 MODIFY (C2 DECRYPT);
--Create encrypted tablespace
SQL> CREATE TABLESPACE ENC_USR DATAFILE '/tibero/examples/tbdata/tibero/enc_usr01.dtf' size 50m ENCRYPTION USING 'AES256' DEFAULT STORAGE (ENCRYPT);
--Move table to encrypted tablespace
SQL> ALTER TABLE TIBERO.T2 MOVE TABLESPACE ENC_USR;
--Query file and block numbers
SQL> select C1,
rowid,
dbms_rowid.rowid_block_number(rowid) as blkno, dbms_rowid.rowid_to_relative_fno(rowid) as fno2
from tibero.t2;
C1 ROWID BLKNO FNO2
---------- ------------------------------------------------------------------------ ---------- ----------
111 AAABA9AAEAAAAAKAAA 10 4
222 AAABA9AAEAAAAAKAAB 10 4
333 AAABA9AAEAAAAAKAAC 10 4
444 AAABA9AAEAAAAALAAA 11 4
555 AAABA9AAEAAAAALAAB 11 4
666 AAABA9AAEAAAAALAAC 11 4
6 rows selected.
--Execute block dump (targeting the 2 blocks queried above)
SQL> alter system dump datafile 4 block 10;
System altered.**Dump start at 2025-12-16 16:28:47
DUMP of BLOCK file #4 block #10
**Dump start at 2025-12-16 16:28:47
data block Dump[dba=04_00000010(16777226),tsn=0000.00014a59,type=13,seqno =1, flag=1]
--------------------------------------------------------------
sgmt_id=4157 cleanout_tsn=0000.00000000 btxcnt=2
l1dba=04_00000007(16777223), offset_in_l1=3
btx xid undo fl tsn/credit
00 0000.00.0000 00_00000000.00000.00000 I 0000.00000000
01 0000.00.0000 00_00000000.00000.00000 I 0000.00000000
--------------------------------------------------------------
Data block dump:
dlhdr_size=14 freespace=2060 freepos=2158 symtab_offset=0 rowcnt=3
Row piece dump:
rp 0 6178: [2010] flag=--H-FL-- itlidx=255 colcnt=2
col 0: [4]
0000: 03 C2 81 8B ....
col 1: [2003]
0000: FE 07 D0 41 20 20 20 20 20 20 20 20 20 20 20 20 ...A
0010: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
0020: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
0030: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
~ ~ omitted ~ ~