New DBA_IMMUTABLE_TABLES view, starting on 19.11

If you follow my quarterly dissecting database patches series, you will note that on 19.11 (April-2021 CPU) oracle introduced a new USER/ALL/DBA_IMMUTABLE_TABLES view.

This seems to be related with the new blockchain feature, but on 19.10 (the previous RU), oracle have already created USER/ALL/DBA_BLOCKCHAIN_TABLES. In this post I will investigate the differences among them.

So, first checking the official documentation, on 19.11 Oracle introduced the concept of "Immutable Tables". We do also have a new syntax for this: CREATE IMMUTABLE TABLE

Reading further the doc, note this is not a simple alias to the CREATE BLOCKCHAIN TABLEcommand we had before. Immutable and Blockchain have key differences:

Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-tables.html#GUID-55B51BC2-6367-4728-87C2-C30E898DD6FD

Immutable Tables seems to be a "lighter version" of Blockchain tables. The basic difference is that there is no chained rows (and thus, no extra commit time and no detection of changes made bypassing the DB layer as the hash of a new line will not depend on the previous ones).

With no chained rows, technically this would also allow massive parallel inserts which may cover one of the biggest restrictions of blockchain.

Getting the views structure, you can see they are extremely similar:

SQL> desc DBA_IMMUTABLE_TABLES
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SCHEMA_NAME                               NOT NULL VARCHAR2(128)
 TABLE_NAME                                NOT NULL VARCHAR2(128)
 ROW_RETENTION                                      NUMBER
 ROW_RETENTION_LOCKED                               VARCHAR2(3)
 TABLE_INACTIVITY_RETENTION                         NUMBER

SQL> desc DBA_BLOCKCHAIN_TABLES
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SCHEMA_NAME                               NOT NULL VARCHAR2(128)
 TABLE_NAME                                NOT NULL VARCHAR2(128)
 ROW_RETENTION                                      NUMBER
 ROW_RETENTION_LOCKED                               VARCHAR2(3)
 TABLE_INACTIVITY_RETENTION                         NUMBER
 HASH_ALGORITHM                                     VARCHAR2(8)

The single difference is an extra "HASH_ALGORITHM" column on DBA_BLOCKCHAIN_TABLES, which is not necessary as we are not creating rows hashes.

Regarding their code:

SQL> set lines 1000 pages 1000 long 1000 longc 1000 pages 0
SQL> select dbms_metadata.get_ddl('VIEW','DBA_IMMUTABLE_TABLES') from dual;

  CREATE OR REPLACE FORCE NONEDITIONABLE VIEW "SYS"."DBA_IMMUTABLE_TABLES" ("SCHEMA_NAME", "TABLE_NAME", "ROW_RETENTION", "ROW_RETENTION_LOCKED", "TABLE_INACTIVITY_RETENTION") AS
  select u.name,
       o.name,
       b.row_retention, decode(bitand(t.spare7, power(2,10)),
                                    power(2,10), 'YES', 'NO'),
       b.table_inactivity_retention
    from sys.obj$ o, sys.user$ u, sys.blockchain_table$ b, sys.tab$ t
    where  o.owner# = u.user# and
           o.obj# = b.obj# and
           o.obj# = t.obj# and bitand(t.spare7,power(2,11)) = power(2,11)
    order by o.obj#

SQL> select dbms_metadata.get_ddl('VIEW','DBA_BLOCKCHAIN_TABLES') from dual;

  CREATE OR REPLACE FORCE NONEDITIONABLE VIEW "SYS"."DBA_BLOCKCHAIN_TABLES" ("SCHEMA_NAME", "TABLE_NAME", "ROW_RETENTION", "ROW_RETENTION_LOCKED", "TABLE_INACTIVITY_RETENTION", "HASH_ALGORITHM") AS
  select u.name,
       o.name,
       b.row_retention, decode(bitand(t.spare7, power(2,10)),
                                                  power(2,10), 'YES', 'NO'),
       b.table_inactivity_retention,
       decode(l.hash_algorithm#, 1, 'SHA2_512', 'NONE')
    from sys.obj$ o, sys.user$ u, sys.blockchain_table_epoch$ l,
         sys.blockchain_table$ b, sys.tab$ t
    where  l.obj# = o.obj# and
           o.owner# = u.user# and
           o.obj# = b.obj# and
           o.obj# = t.obj# and bitand(t.spare7,power(2,7)) = power(2,7)
    order by o.obj#

They are almost exactly the same. I can see 2 differences:

• The "HASH_ALGORITHM" again which implied on the sys.blockchain_table_epoch$ addition.
• For the DBA_IMMUTABLE_TABLES, it's checking the 11th bit of spare7 for a "1" while that for DBA_BLOCKCHAIN_TABLES, it's checking the 7th bit.

What about default privileges?

SQL> select * from dba_tab_privs where table_name in ('DBA_IMMUTABLE_TABLES','DBA_BLOCKCHAIN_TABLES');

GRANTEE              OWNER  TABLE_NAME             GRANTOR  PRIVILEGE  GRA HIE COM TYPE  INH
-------------------- ------ ---------------------- -------- ---------- --- --- --- ----- ---
SELECT_CATALOG_ROLE  SYS    DBA_BLOCKCHAIN_TABLES  SYS      SELECT     NO  NO  YES VIEW  NO
SELECT_CATALOG_ROLE  SYS    DBA_IMMUTABLE_TABLES   SYS      SELECT     NO  NO  YES VIEW  NO
SQL>

They are also the same..

Conclusion

It seems to me that Immutable Tables are, generally speaking, the blockchain tables with all that hidden columns (like ORABCTAB_CREATION_TIME$) that will ensure that a row will not be deleted (or table be dropped) during the user defined retention period.

Removing the main concept of chained rows (or "blockchain"), this will add much more performance power to it, while will remove the key security feature.

+4