📋Steps in this guide1/7

Immutable Table Enhancements in Oracle Database 23ai/26ai

Immutable tables were introduced to Oracle 21.3 and 19.11 at the same time, so it could be considered a 19c and 21c new feature. This article demonstrates the enhancements to immutable tables in Oracle 23ai/26ai.

oracle 23configurationintermediate

by OracleDba

39 views

Create an Immutable Table

In addition to adding the keyword to the command, there are two immutable clauses. The clause determines how long the table is protected from being dropped. If the table has no rows it can still be dropped. Unlike the initial releases of blockchain tables, the clause also prevents the table being dropped via a command. - : The table can't be dropped. Be careful about using this setting during testing. - : The table can't dropped until there have been no new rows inserted for the specified number of days. You may prefer to use 0 or 1 as the number of days during testing this functionality. The clause determines the retention period. How long each row will be protected from deletion. - : Each row is retained forever. The absence of the keyword implies the setting can be changed with the command, but it can't. Retention periods can only be increased. - : Same as . - : Each row is protected from deletion for the specified number of days, but this setting can be increased using the command. Minimum 16 days. - : Each row is protected from deletion for the specified number of days, and this setting can't be changed using the command. Minimum 16 days. In Oracle 23ai/26ai we have the option of using the original "V1" version, which is the default, or the new "V2" version, which supports additional functionality. The following example creates two tables. One of each version. Checking the view shows us several invisible columns have been added to our column list. The hidden columns are the same as those of a blockchain table, but unlike blockchain tables, only the and columns are populated. The rest of the columns are set to null. The hidden columns are described here . Notice the "V2" table has twice the number of hidden columns compared to the "V1" table. The views display information about immutable tables. It's a view over the table.

Code/Command (click line numbers to comment):

NO DROP [ UNTIL number DAYS IDLE ]

NO DELETE { [ LOCKED ] | (UNTIL number DAYS AFTER INSERT [ LOCKED ]) }

drop table if exists 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 0 days idle
no delete until 16 days after insert;


drop table if exists it_t2 purge;

create immutable table it_t2 (
  id            number,
  fruit         varchar2(20),
  quantity      number,
  created_date  date,
  constraint it_t2_pk primary key (id)
)
no drop until 0 days idle
no delete until 16 days after insert version "v2";

set linesize 120 pagesize 50
column column_name format a45
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                             20 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>


select internal_column_id,
       column_name,
       data_type,
       data_length,
       hidden_column
FROM   user_tab_cols       
WHERE  table_name = 'IT_T2'
ORDER BY internal_column_id;

INTERNAL_COLUMN_ID COLUMN_NAME                                   DATA_TYPE                   DATA_LENGTH HIDDEN_COLUMN
------------------ --------------------------------------------- --------------------------- ----------- -------------
                 1 ID                                            NUMBER                               22 NO
                 2 FRUIT                                         VARCHAR2                             20 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                                1000 YES
                14 ORABCTAB_SPARE$                               RAW                                2000 YES
                15 ORABCTAB_PDB_GUID$                            RAW                                2000 YES
                16 ORABCTAB_ROW_VERSION$                         NUMBER                               22 YES
                17 ORABCTAB_LAST_ROW_VERSION_NUMBER$             RAW                                   1 YES
                18 ORABCTAB_USER_CHAIN_HASH$                     RAW                                2000 YES
                19 ORABCTAB_DELEGATE_SIGNATURE$                  RAW                                2000 YES
                20 ORABCTAB_DELEGATE_SIGNATURE_ALG$              NUMBER                               22 YES
                21 ORABCTAB_DELEGATE_SIGNATURE_CERT$             RAW                                1000 YES
                22 ORABCTAB_DELEGATE_USER_NUMBER$                NUMBER                               22 YES
                23 ORABCTAB_COUNTERSIGNATURE$                    RAW                                2000 YES
                24 ORABCTAB_COUNTERSIGNATURE_ALG$                NUMBER                               22 YES
                25 ORABCTAB_COUNTERSIGNATURE_CERT$               RAW                                1000 YES
                26 ORABCTAB_COUNTERSIGNATURE_ROW_FORMAT_VERSION$ VARCHAR2                           4000 YES
                27 ORABCTAB_COUNTERSIGNATURE_ROW_FORMAT_FLAG$    NUMBER                               22 YES
                28 ORABCTAB_TS$                                  TIMESTAMP(6)                         13 YES

28 rows selected.

SQL>

column table_name format a10
column row_retention format 9999999999999
column row_retention_locked format a20
column table_inactivity_retention format 99999999999999999999999999

SELECT table_name,
       row_retention,
       row_retention_locked, 
       table_inactivity_retention
FROM   user_immutable_tables
order by 1;

TABLE_NAME  ROW_RETENTION ROW_RETENTION_LOCKED  TABLE_INACTIVITY_RETENTION
---------- -------------- -------------------- ---------------------------
IT_T1                  16 NO                                             0
IT_T2                  16 NO                                             0

SQL>

Alter an Immutable Table

The following operations perform the same on "V1" and "V2" blockchain tables. The clause can be altered using the command, as long as the retention period is not reduced. Be careful not to set it too large when testing. Regardless of the current drop delay setting, an attempt to switch to the maximum value of causes an error. This is because of the parameter discussed below. Assuming it was not defined as locked, the clause can be modified using the command, as long as the retention period is not reduced. We currently have a row retention period of 16 days. In the example below we increase that value to 32. When we subsequently attempt to lower the value to 16 it gives an error. We can set the row retention to , which means rows will be kept forever. Remember, we can't reduce this value once is it set, so take care using such a long retention period. We'll drop and recreate the "V1" immutable table. We've not inserted any rows into it yet, so despite the retention times the following command works.

Code/Command (click line numbers to comment):

alter table it_t1 no drop until 1 days idle;

alter table it_t1 no drop;
*
ERROR at line 1:
ORA-05807: Blockchain or immutable table "TESTUSER1"."IT_T1" cannot have idle retention greater than 16 days.

SQL>

-- 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>

alter table it_t1 no delete;

drop table if exists 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 0 days idle
no delete until 16 days after insert;

Blocked DML and DDL Operations

As you would expect for an insert-only table, all DML and DDL operations that would result in row data being amended or deleted are prevented for an immutable table. The following example shows a successful insert, then some unsuccessful DML statements. Some DDL statements that could alter the contents of the data are also prevented. Here is an example of the statement. Extending existing columns is fine.

Code/Command (click line numbers to comment):

-- INSERT
insert into it_t2 (id, fruit, quantity, created_date ) values (1, 'apple', 20, sysdate);

1 row inserted.

SQL> commit;

Commit complete.

SQL>


-- UPDATE
update it_t2 set quantity = 10 where id = 1;
       *
ERROR at line 1:
ORA-05715: operation not allowed on the blockchain or immutable table

SQL>


-- DELETE
delete from it_t2 where id = 1;
            *
ERROR at line 1:
ORA-05715: operation not allowed on the blockchain or immutable table

SQL>

truncate table it_t1;
               *
ERROR at line 1:
ORA-05715: operation not allowed on the blockchain or immutable table

SQL>

alter table it_t2 modify (fruit varchar2(25));

Table altered.

SQL>

Add/Drop Columns

In a "V1" immutable table adding new columns or dropping existing columns is not allowed. In a "V2" immutable table we can add new columns and drop existing columns. The dropped columns are marked as hidden, rather than actually being dropped.

Code/Command (click line numbers to comment):

-- Add column
alter table it_t1 add (additional_info varchar2(50));
*
ERROR at line 1:
ORA-05715: operation not allowed on the blockchain or immutable table

SQL>


-- Drop column.
alter table it_t1 drop column quantity;
*
ERROR at line 1:
ORA-05715: operation not allowed on the blockchain or immutable table

SQL>

-- Add column
alter table it_t2 add (additional_info varchar2(50));

Table altered.

SQL>


-- Drop column.
alter table it_t2 drop column additional_info;

Table altered.

SQL>

Control Long Idle Retention Times

In Oracle 23ai/26ai the maximum idle retention time for the table is controlled by the parameter. This means we can't set the clause to a value longer than 16 days. We can either increase this setting, or grant the user the privilege, which allows the user to ignore this limit. Now we can ignore the limit.

Code/Command (click line numbers to comment):

show parameter blockchain_table_retention_threshold

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
blockchain_table_retention_threshold integer     16
SQL>

drop table if exists it_t3 purge;

create immutable table it_t3 (
  id            number,
  fruit         varchar2(20),
  quantity      number,
  created_date  date,
  constraint it_t3_pk primary key (id)
)
no drop until 32 days idle
no delete until 16 days after insert;
*
ERROR at line 1:
ORA-05807: Blockchain or immutable table "TESTUSER1"."IT_T3" cannot have idle retention greater than 16 days.

SQL>

conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

grant table retention to testuser1;

conn testuser1/testuser1@//localhost:1521/freepdb1

drop table if exists itt_t3 purge;

create immutable table it_t3 (
  id            number,
  fruit         varchar2(20),
  quantity      number,
  created_date  date,
  constraint it_t3_pk primary key (id)
)
no drop until 32 days idle
no delete until 16 days after insert;

Table created.

SQL>

DBMS_IMMUTABLE_TABLE Package

The package is used for maintenance of immutable tables. The procedure removes any rows that are beyond the retention period. They can't be removed using a normal statement. Alternatively, we can limit the deletion by date. The rows will only be deleted if they are outside the retention period, and match the date criteria.

Code/Command (click line numbers to comment):

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>

Considerations

There are a number of things to consider when using immutable tables. - Immutable tables can be indexed and partitioned in the normal manner. - There are a number of general restrictions associated with blockchain tables, described here . I guess the main question should be, why would you use an immutable table? - If you need an insert-only tamper proof table in your application generally, this could be the solution. - If you want the added security of cryptographic hashes, you might want to consider blockchain tables. For more information see: Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!