Oracle数据库中的不可变表(19c、21c)

山东Oracle用户组 2021-08-03

946

不可变表是一个防篡改、仅能插入的表，具有相关联的表级和行级保留周期。它们类似于区块链表，但行没有使用加密散列链接。

区块链表在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版本的总体感觉是，不可变表的实现在这一点上是有缺陷的。有一些特性没有按照文档所描述的那样工作，导致错误消息不准确，或者不能正确捕获。

不可变表可以按照正常的方式进行索引和分区。

如果您的应用程序需要一个只允许插入的防篡改表，那么这可能是一个解决方案。

如果希望增加加密散列的安全性，可以考虑区块链表。

oracle

文章转载自山东Oracle用户组，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Oracle数据库中的不可变表(19c、21c)

DBMS_IMMUTABLE_TABLE 包

评论

相关阅读