{"id":13737,"date":"2021-04-30T11:57:58","date_gmt":"2021-04-30T14:57:58","guid":{"rendered":"https:\/\/www.dbarj.com.br\/?p=13737"},"modified":"2021-04-30T15:34:04","modified_gmt":"2021-04-30T18:34:04","slug":"new-dba_immutable_tables-view-starting-on-19-11","status":"publish","type":"post","link":"https:\/\/www.dbarj.com.br\/en\/2021\/04\/new-dba_immutable_tables-view-starting-on-19-11\/","title":{"rendered":"New DBA_IMMUTABLE_TABLES view, starting on 19.11"},"content":{"rendered":"

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

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. <\/strong>In this post I will investigate the differences among them.<\/p>\n

So, first checking the official documentation<\/a>, on 19.11 Oracle introduced the concept of “Immutable Tables”. We do also have a new syntax for this: CREATE IMMUTABLE TABLE<\/code><\/p>\n

Reading further the doc, note this is not<\/span><\/strong> a simple alias to the CREATE BLOCKCHAIN TABLE<\/code>command we had before. Immutable and Blockchain have key differences:<\/p>\n

\n\n\n\n\n\n\n\n
Immutable Tables<\/th>\nBlockchain Tables<\/th>\n<\/tr>\n<\/thead>\n
Immutable tables prevent unauthorized changes by rogue or compromised insiders who have access to user credentials.<\/td>\nIn addition to preventing unauthorized changes by rogue or compromised insiders, blockchain tables provide the following functionality:<\/p>\n
    \n
  • detection of unauthorized changes made by bypassing Oracle Database software<\/li>\n
  • detection of end user impersonation and inserting data in a user’s name but without their authorization<\/li>\n
  • prevention of data tampering and ensuring that data was actually inserted in to the table<\/li>\n<\/ul>\n<\/td>\n<\/tr>\n
Rows are not chained together.<\/td>\nEach row, except the first row, is chained to the previous row by using a cryptographic hash. The hash value of a row is computed based on the row data and the hash value of the previous row in the chain.<\/p>\n

Any modification to a row breaks the chain, thereby indicating that the row was tampered.<\/td>\n<\/tr>\n

Inserting rows does not require additional processing at commit time.<\/td>\nAdditional processing time is required, at commit time, to chain rows.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

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

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).<\/p>\n

With no chained rows, technically this would also allow massive parallel inserts which may cover one of the biggest restrictions of blockchain.<\/p>\n

Getting the views structure, you can see they are extremely similar:<\/p>\n

SQL> desc DBA_IMMUTABLE_TABLES\r\n Name                                      Null?    Type\r\n ----------------------------------------- -------- ----------------------------\r\n SCHEMA_NAME                               NOT NULL VARCHAR2(128)\r\n TABLE_NAME                                NOT NULL VARCHAR2(128)\r\n ROW_RETENTION                                      NUMBER\r\n ROW_RETENTION_LOCKED                               VARCHAR2(3)\r\n TABLE_INACTIVITY_RETENTION                         NUMBER\r\n\r\nSQL> desc DBA_BLOCKCHAIN_TABLES\r\n Name                                      Null?    Type\r\n ----------------------------------------- -------- ----------------------------\r\n SCHEMA_NAME                               NOT NULL VARCHAR2(128)\r\n TABLE_NAME                                NOT NULL VARCHAR2(128)\r\n ROW_RETENTION                                      NUMBER\r\n ROW_RETENTION_LOCKED                               VARCHAR2(3)\r\n TABLE_INACTIVITY_RETENTION                         NUMBER\r\n HASH_ALGORITHM                                     VARCHAR2(8)<\/pre>\n
The single difference is an extra “HASH_ALGORITHM<\/strong>” column on DBA_BLOCKCHAIN_TABLES<\/strong>, which is not necessary as we are not creating rows hashes.<\/p>\n
Regarding their code:<\/p>\n
SQL> set lines 1000 pages 1000 long 1000 longc 1000 pages 0\r\nSQL> select dbms_metadata.get_ddl('VIEW','DBA_IMMUTABLE_TABLES') from dual;\r\n\r\n  CREATE OR REPLACE FORCE NONEDITIONABLE VIEW \"SYS\".\"DBA_IMMUTABLE_TABLES\" (\"SCHEMA_NAME\", \"TABLE_NAME\", \"ROW_RETENTION\", \"ROW_RETENTION_LOCKED\", \"TABLE_INACTIVITY_RETENTION\") AS\r\n  select u.name,\r\n       o.name,\r\n       b.row_retention, decode(bitand(t.spare7, power(2,10)),\r\n                                    power(2,10), 'YES', 'NO'),\r\n       b.table_inactivity_retention\r\n    from sys.obj$ o, sys.user$ u, sys.blockchain_table$ b, sys.tab$ t\r\n    where  o.owner# = u.user# and\r\n           o.obj# = b.obj# and\r\n           o.obj# = t.obj# and bitand(t.spare7,power(2,11)) = power(2,11)\r\n    order by o.obj#\r\n\r\nSQL> select dbms_metadata.get_ddl('VIEW','DBA_BLOCKCHAIN_TABLES') from dual;\r\n\r\n  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\r\n  select u.name,\r\n       o.name,\r\n       b.row_retention, decode(bitand(t.spare7, power(2,10)),\r\n                                                  power(2,10), 'YES', 'NO'),\r\n       b.table_inactivity_retention,\r\n       decode(l.hash_algorithm#, 1, 'SHA2_512', 'NONE')\r\n    from sys.obj$ o, sys.user$ u, sys.blockchain_table_epoch$ l,\r\n         sys.blockchain_table$ b, sys.tab$ t\r\n    where  l.obj# = o.obj# and\r\n           o.owner# = u.user# and\r\n           o.obj# = b.obj# and\r\n           o.obj# = t.obj# and bitand(t.spare7,power(2,7)) = power(2,7)\r\n    order by o.obj#<\/pre>\n
They are almost<\/span><\/strong> exactly the same. I can see 2 differences:<\/p>\n