Oracle数据库中的不可变表(19c、21c)
Oracle数据库中的不可变表(19c、21c)
不可变表是一个防篡改、仅能插入的表，具有相关联的表级和行级保留周期。它们类似于区块链表，但行没有使用加密散列链接。
区块链表在21c中被引入，并被反向移植到19c中，所以可以认为它是19c和21c的新特性。
在学习不可变表时，注意不要设置太长的保留期，否则将不得不等待很长时间来删除您的测试表。
前提条件
COMPATIBLE 参数必须设置为正确的值，才能使用不可变表。
conn / as sysdba
# 19c
alter system set compatible='19.11.0' scope=spfile;
# 21c
alter system set compatible='21.3.0' scope=spfile;
shutdown immediate;
startup;建议在增加 COMPATIBLE 参数时要非常谨慎。如果非必要不要修改。
创建一个不可变的表
除了在 CREATE TABLE 命令中添加 IMMUTABLE 关键字外，还有两个不可变子句。
NO DROP 子句确定保护表不被删除的时间。如果表没有行，它仍然可以被删除。与区块链表的初始版本不同，NO DROP 子句还防止表通过 DROP USER ... CASCADE 级联删除。
NO DROP [ UNTIL number DAYS IDLE ]- NO DROP：不能删除表。在测试期间使用此设置时要小心。
- NO DROP UNTIL number DAYS IDLE：在指定天数内没有插入新行之前，不能删除表。您可能更喜欢使用0或1作为测试此功能的天数。
NO DELETE 子句决定了保留期限。每一行被保护不被删除的时间。
NO DELETE { [ LOCKED ] | (UNTIL number DAYS AFTER INSERT [ LOCKED ]) }- NO DELETE：每一行永远保留。没有LOCKED关键字意味着可以用ALTER TABLE命令更改设置，但不能。保留期只能增加。
- NO DELETE LOCKED：同NO DELETE。
- NO DELETE UNTIL number DAYS AFTER INSERT：在指定的天数内，保护每一行不被删除，但可以使用ALTER TABLE命令增加此设置。最低16天。
- NO DELETE UNTIL number DAYS AFTER INSERT LOCKED：在指定的天数内，保护每一行不被删除，不能使用ALTER TABLE命令更改此设置。最低16天。
把示例如下：
--drop table it_t1 purge;
create immutable table it_t1 (
id number,
fruit varchar2(20),
quantity number,
created_date date,
constraint it_t1_pk primary key (id)
)
no drop until 1 days idle
no delete until 16 days after insert;通过检查 USER_TAB_COLS 视图，我们可以看到一些不可见的列被添加到列列表中。隐藏列与区块链表相同，但与区块链表不同的是，只有 ORABCTAB_CREATION_TIME 和 ORABCTAB_USER_NUMBER 列被填充数据。其余的列值为空。
set linesize 120 pagesize 50
column column_name format a30
column data_type format a27
column hidden_column format a13
select internal_column_id,
column_name,
data_type,
data_length,
hidden_column
FROM user_tab_cols
WHERE table_name = 'IT_T1'
ORDER BY internal_column_id;
INTERNAL_COLUMN_ID COLUMN_NAME DATA_TYPE DATA_LENGTH HIDDEN_COLUMN
------------------ ------------------------------ --------------------------- ----------- -------------
1 ID NUMBER 22 NO
2 FRUIT VARCHAR2 25 NO
3 QUANTITY NUMBER 22 NO
4 CREATED_DATE DATE 7 NO
5 ORABCTAB_INST_ID$ NUMBER 22 YES
6 ORABCTAB_CHAIN_ID$ NUMBER 22 YES
7 ORABCTAB_SEQ_NUM$ NUMBER 22 YES
8 ORABCTAB_CREATION_TIME$ TIMESTAMP(6) WITH TIME ZONE 13 YES
9 ORABCTAB_USER_NUMBER$ NUMBER 22 YES
10 ORABCTAB_HASH$ RAW 2000 YES
11 ORABCTAB_SIGNATURE$ RAW 2000 YES
12 ORABCTAB_SIGNATURE_ALG$ NUMBER 22 YES
13 ORABCTAB_SIGNATURE_CERT$ RAW 16 YES
14 ORABCTAB_SPARE$ RAW 2000 YES
14 rows selected.
SQL>{CDB|DBA|ALL|USER}_IMMUTABLE_TABLES 视图显示关于不可变表的信息。
column row_retention format a13
column row_retention_locked format a20
column table_inactivity_retention format a26
SELECT row_retention,
row_retention_locked,
table_inactivity_retention
FROM user_immutable_tables
WHERE table_name = 'IT_T1';
ROW_RETENTION ROW_RETENTION_LOCKED TABLE_INACTIVITY_RETENTION
------------- -------------------- --------------------------
16 NO 0
SQL>修改一个不可变的表
文档建议可以使用 ALTER TABLE 命令修改 NO DROP 子句，只要不减少保留期。在编写本文时，这似乎不适用于最初创建时没有 NO DROP UNTIL 0 DAYS IDLE 的表，因为所有的天值都会返回一个错误。我们目前的保留期为 0 天的表。在下面的示例中，我们尝试将其更改为100天，这将给出一个错误。这个命令在语法上是正确的，所以我假设这是这个版本更新中的一个 bug。
alter table it_t1 no drop until 100 days idle;
Error report -
ORA-05732: retention value cannot be lowered
SQL>这个命令可以在没有 NO DROP UNTIL 1 DAYS IDLE 或更高时间的表上执行。
不管当前的 drop 延迟设置如何，尝试切换到 NO drop 的最大值都会导致 ORA-00600 错误。
alter table it_t1 no drop;
Error starting at line : 1 in command -
alter table it_t1 no drop
Error report -
ORA-00600: internal error code, arguments: [atbbctable_1], [0], [], [], [], [], [], [], [], [], [], []这是一个问题，因为我认为大多数人都希望以零延迟开始执行。使用 NO DROP 非常危险，因为删除表的唯一方法是删除整个模式。
假设 NO DELETE 子句没有定义为 locked，只要不减少保留周期，就可以使用 ALTER TABLE 命令修改 NO DELETE 子句。我们目前有16天的行保留期。在下面的示例中，我们将该值增加到32。当我们随后试图将该值降低到16时，会出现错误。
-- Increase to 32 days.
alter table it_t1 no delete until 32 days after insert;
Table IT_T1 altered.
SQL>
-- Decrease to 16 days (fail).
alter table it_t1 no delete until 16 days after insert;
Error report -
ORA-05732: retention value cannot be lowered
SQL>在当前版本中，试图将行保留设置为 NO DELETE，这将增加保留周期，将导致一个 ORA-00600 错误。我假设这是当前版本更新中的一个bug。
alter table it_t1 no delete;
Error report -
ORA-00600: internal error code, arguments: [atbbctable_1], [0], [], [], [], [], [], [], [], [], [], []阻塞的DML和DDL操作
对于不可变表，所有导致行数据修改或删除的DML和DDL操作都将被阻止。
下面的示例显示了一个成功的插入，然后是一些不成功的DML语句。
-- INSERT
insert into it_t1 (id, fruit, quantity, created_date ) values (1, 'apple', 20, sysdate);
1 row inserted.
SQL> commit;
Commit complete.
SQL>
-- UPDATE
update it_t1 set quantity = 10 where id = 1;
Error report -
SQL Error: ORA-05715: operation not allowed on the blockchain or immutable table
SQL>
-- DELETE
delete from it_t1 where id = 1;
Error report -
SQL Error: ORA-05715: operation not allowed on the blockchain or immutable table
SQL>一些可能改变数据内容的DDL语句也被阻止了。
下面是一个 TRUNCATE 语句的例子。
truncate table it_t1;
Error report -
ORA-05715: operation not allowed on the blockchain or immutable table
SQL>扩展现有列是可以的，但不允许添加新列或删除现有列。
-- Extend column.
alter table it_t1 modify (fruit varchar2(25));
Table IT_T1 altered.
SQL>
-- Add column
alter table it_t1 add (additional_info varchar2(50));
Error report -
ORA-05715: operation not allowed on the blockchain or immutable table
SQL>
-- Drop column.
alter table it_t1 drop column quantity;
Error report -
ORA-05715: operation not allowed on the blockchain or immutable table
SQL>DBMS_IMMUTABLE_TABLE 包
DBMS_IMMUTABLE_TABLE 包用于维护不可变表。
DELETE_EXPIRED_ROWS 存储过程删除任何超过保留期的行。不能使用普通的 DELETE 语句删除它们。
set serveroutput on
declare
l_rows number;
begin
dbms_immutable_table.delete_expired_rows(
schema_name => 'testuser1',
table_name => 'it_t1',
before_timestamp => null,
number_of_rows_deleted => l_rows);
dbms_output.put_line('Rows Deleted=' || l_rows);
end;
/
Rows Deleted=0
PL/SQL procedure successfully completed.
SQL>或者，我们可以根据日期限制删除。只有当这些行超出保留期并且符合日期标准时，它们才会被删除。
set serveroutput on
declare
l_rows number;
begin
dbms_immutable_table.delete_expired_rows(
schema_name => 'testuser1',
table_name => 'it_t1',
before_timestamp => systimestamp - 60,
number_of_rows_deleted => l_rows);
dbms_output.put_line('Rows Deleted=' || l_rows);
end;
/
Rows Deleted=0
PL/SQL procedure successfully completed.
SQL>注意事项
在使用不可变表时，有许多事情需要考虑。
对19.3和21.3版本的总体感觉是，不可变表的实现在这一点上是有缺陷的。有一些特性没有按照文档所描述的那样工作，导致错误消息不准确，或者不能正确捕获。
不可变表可以按照正常的方式进行索引和分区。
如果您的应用程序需要一个只允许插入的防篡改表，那么这可能是一个解决方案。
如果希望增加加密散列的安全性，可以考虑区块链表。