Oracle IMMUTABLE TABLES
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
Purpose Immutable tables are intended to provide an extremely tamper resistant means of storing relational data in a form wherein it can be accessed using SQL. Immutable tables intended for use in an environment where it is required that an audit trail could potentially be tampered with my insertion but that once a record was inserted it would not be possible to alter or delete it except within the date constraints imposed as part of the NO DROP and NO DELETE clauses.
Data Dictionary Objects
ALL_SEGMENTS PROCEDUREBLOCKCHAININFO$ DBA_TABLES
ALL_TAB_COLS CDB_SEGMENTS DBMS_BLOCKCHAIN_TABLE_LIB
ALL_TABLES CDB_TAB_COLS USER_SEGMENTS
BLOCKCHAIN_TABLE$ CDB_TABLES USER_TAB_COLS
BLOCKCHAIN_TABLE_CHAIN$ DBA_SEGMENTS USER_TABLES
BLOCKCHAIN_TABLE_EPOCH$ DBA_TAB_COLS  
Exceptions
Error Code Reason
ORA-05715 operation not allowed on the blockchain or immutable table
ORA-05723 drop blockchain or immutable table <table_name> not allowed
ORA-05729 blockchain or immutable table cannot be created in root container
First Available 21c, back-ported to 19.11
System Privileges
ALTER ANY TABLE CREATE ANY TABLE DROP ANY TABLE
ALTER TABLE CREATE TABLE DROP TABLE
 
General (new 21c)
Block Change table CREATE IMMUTABLE TABLE <schema_name>.<table_name>(
<column_name> <column_data_type>)
NO DROP [UNTIL <integer> DAYS IDLE]
[sharing_clause]
[memoptimize_clause]
[relational_properties];
CREATE IMMUTABLE TABLE uwclass.audit1(
tx_id    INTEGER,
tx_date  DATE,
tx_value NUMBER(10,2))
NO DROP UNTIL 31 DAYS IDLE
NO DELETE UNTIL 31 DAYS AFTER INSERT
TABLESPACE uwdata;

Table created.
Block Change table with LOCKED CREATE IMMUTABLE TABLE <schema_name>.<table_name>(
<column_name> <column_data_type>)
NO DROP [UNTIL <integer> DAYS IDLE]
NO DELETE UNTIL <integer> DAYS AFTER INSERT [LOCKED]
[sharing_clause]
[memoptimize_clause]
[relational_properties];
CREATE IMMUTABLE TABLE uwclass.audit2(
tx_id    INTEGER,
tx_date  DATE,
tx_value NUMBER(10,2))
NO DROP UNTIL 31 DAYS IDLE
NO DELETE UNTIL 31 DAYS AFTER INSERT LOCKED
TABLESPACE uwdata;

Table created.
Immutable table with range partitioning

This was enabled in 20c but is not available in 21.1.
CREATE IMMUTABLE TABLE <schema_name>.<table_name>(
<column_name> <column_data_type>)
NO DROP UNTIL
NO DELETE LOCKED
PARTITION BY ....;
CREATE IMMUTABLE TABLE uwclass.audit_partitioned(
tx_id     NUMBER,
tx_date   DATE,
grantor   VARCHAR2(60),
grantee   VARCHAR2(60))
NO DROP UNTIL 60 DAYS IDLE
NO DELETE UNTIL 31 DAYS AFTER INSERT
PARTITION BY RANGE(trans_date) (
 PARTITION p1 VALUES LESS THAN (TO_DATE('01-01-2018','dd-mm-yyyy')),
 PARTITION p2 VALUES LESS THAN (TO_DATE('01-01-2019','dd-mm-yyyy')),
 PARTITION p3 VALUES LESS THAN (TO_DATE('01-01-2020','dd-mm-yyyy')),
 PARTITION p4 VALUES LESS THAN (TO_DATE('01-01-2021','dd-mm-yyyy')));

Table created.
Immutable Table Reverse Engineered -- reformatted for legibility

SELECT dbms_metadata.get_ddl('TABLE', 'AUDIT2','UWCLASS') FROM dual;

CREATE TABLE "UWCLASS"."AUDIT2" (
"TX_ID"    NUMBER(*,0),
"TX_DATE"  DATE,
"TX_VALUE" NUMBER(10,2)
SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "UWDATA"


-- two very important things to consider with respect to the above metadata
-- first, omits the fact that it is an immutable table ... compare with the
-- blockchain table which explicitly identifies itself.

-- the second difference is the lack of an appended anonymous PL/SQL block
-- perhaps this is an omission and perhaps there isn't any PL/SQL code
-- our suspicion is that it is an omission and dbms_metatadata needs a fix.


SELECT * FROM blockchain_table_chain$;

no rows selected

SELECT obj#, epoch#, pdb_guid, owner, object_name
FROM sys.blockchain_table_epoch$ bte, sys.dba_objects do
WHERE bte.obj# = do.object_id;

   OBJ#  EPOCH# PDB_GUID                         OWNER      OBJECT_NAME
------- ------- -------------------------------- ---------- ------------
  76001       1 B6355315C77F2BECE0531000000A714D UWCLASS    LEDGER2
  76070       1 B6355315C77F2BECE0531000000A714D UWCLASS    LEDGER1
  82757       1 B6355315C77F2BECE0531000000A714D C##UWCLASS LEDGER1
  82759       1 B6355315C77F2BECE0531000000A714D C##UWCLASS AUDIT1
  83034       1 B6355315C77F2BECE0531000000A714D UWCLASS    AUDIT1
  83035       1 B6355315C77F2BECE0531000000A714D UWCLASS    AUDIT2


SELECT obj#, hash_algorithm#, hash_data_format_version#, reason#
FROM sys.blockchain_table_epoch$
ORDER BY 1;

   OBJ# HASH_ALGORITHM# HASH_DATA_FORMAT_VERSION# REASON#
------- --------------- ------------------------- --------
  76001               1                         1       1
  76070               1                         1       1
  82757               1                         1       1
  82759               1                         1       1
  83034               1                         1       1
  83035               1                         1       1
 
Immutable Table Demos
  conn / as sysdba

CREATE IMMUTABLE TABLE audit0 (
tx_id    INTEGER,
tx_date  DATE,
tx_value NUMBER(10,2))
NO DROP UNTIL 31 DAYS IDLE
NO DELETE LOCKED;
CREATE IMMUTABLE TABLE audit0 (
*
ERROR at line 1:
ORA-05729: blockchain or immutable table cannot be created in root container


ALTER SESSION SET CONTAINER=PDBDEV;

CREATE IMMUTABLE TABLE uwclass.audit0(
tx_id    INTEGER,
tx_date  DATE,
tx_value NUMBER(10,2))
NO DROP UNTIL 31 DAYS IDLE
NO DELETE LOCKED;

Table created.

SQL> desc uwclass.audit0
Name                          Null?    Type
----------------------------- -------- --------------------
TX_ID                                  NUMBER(38)
TX_DATE                                DATE
TX_VALUE                               NUMBER(10,2)


SELECT column_id, column_name, data_type, hidden_column, virtual_column, user_generated
FROM dba_tab_cols
WHERE owner = 'UWCLASS'
AND table_name = 'AUDIT0'
ORDER BY user_generated, column_id;

COLUMN_ID  COLUMN_NAME               DATA_TYPE                    HID VIR USE
---------- ------------------------- ---------------------------- --- --- ---
           ORABCTAB_CREATION_TIME$   TIMESTAMP(6) WITH TIME ZONE  YES NO  NO
           ORABCTAB_SIGNATURE_CERT$  RAW                          YES NO  NO
           ORABCTAB_SIGNATURE_ALG$   NUMBER                       YES NO  NO
           ORABCTAB_INST_ID$         NUMBER                       YES NO  NO
           ORABCTAB_CHAIN_ID$        NUMBER                       YES NO  NO
           ORABCTAB_SEQ_NUM$         NUMBER                       YES NO  NO
           ORABCTAB_SPARE$           RAW                          YES NO  NO
           ORABCTAB_USER_NUMBER$     NUMBER                       YES NO  NO
           ORABCTAB_HASH$            RAW                          YES NO  NO
           ORABCTAB_SIGNATURE$       RAW                          YES NO  NO
         1 TX_ID                     NUMBER                       NO  NO  YES
         2 TX_DATE                   DATE                         NO  NO  YES
         3 TX_VALUE                  NUMBER                       NO  NO  YES


INSERT INTO uwclass.audit0
(tx_id, tx_date, tx_value)
VALUES
(1, SYSDATE, 100);

1 row created.

COMMIT;

Commit complete.

SELECT * FROM uwclass.audit0;

 TX_ID     TX_DATE           TX_VALUE
------ -------------------- ---------
     1 24-AUG-2021 00:21:45       100


col ORABCTAB_CREATION_TIME$ format a40
col ORABCTAB_HASH$ format a130

SELECT orabctab_user_number$, orabctab_creation_time$, orabctab_inst_id$
FROM uwclass.audit0;

ORABCTAB_USER_NUMBER$ ORABCTAB_CREATION_TIME$                ORABCTAB_INST_ID$
--------------------- -------------------------------------- -----------------
                  110 24-AUG-21 12.21.45.261994 AM +00:00


SELECT object_name, object_type
FROM dba_objects_ae
WHERE object_name = 'AUDIT0';

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------------
AUDIT                          TABLE


SELECT object_name, object_type
FROM dba_objects_ae
WHERE owner = 'UWCLASS'
AND object_name = 'AUDIT0';

OBJECT_TYPE
-------------------
TABLE


SELECT table_name, table_type
FROM dba_all_tables
WHERE owner = 'UWCLASS'
AND table_name = 'AUDIT0';

TABLE_NAME      TABLE_TYPE
--------------- -------------------
AUDIT


SELECT segment_name, segment_type
FROM dba_segments
WHERE owner = 'UWCLASS'
AND segment_name = 'AUDIT0';

SEGMENT_NAME                   SEGMENT_TYPE
------------------------------ ------------------
AUDIT0                         TABLE


UPDATE audit0 SET tx_value = 200;
UPDATE audit0 SET tx_value = 200
*
ERROR at line 1:
ORA-05715: operation not allowed on the blockchain or immutable table


DELETE FROM audit0;
DELETE FROM audit0
*
ERROR at line 1:
ORA-05715: operation not allowed on the blockchain or immutable table

TRUNCATE TABLE audit0;
TRUNCATE TABLE audit0
*
ERROR at line 1:
ORA-05715: operation not allowed on the blockchain table


DROP TABLE audit0;
DROP TABLE audit0;
*
ERROR at line 1:
ORA-05723: drop blockchain or immutable table AUDIT0 not allowed
 
Immutable Table Queries
TAB$ Query SELECT so.name, st.spare7
FROM sys.obj$ so, sys.tab$ st
WHERE so.obj# = st.obj#
AND ctime > sysdate-1/48

NAME         SPARE7
----------- -------
HEAPTAB              -- heap
LEDGER1         128  -- blockchain
AUDIT1         2048  -- immutable

Related Topics
Built-in Functions
Built-in Packages
Database Security
Blockchain Tables
DBMS_BLOCKCHAIN_TABLE
DBMS_BLOCKCHAIN_UTL
DBMS_IMMUTABLE_TABLE
DBMS_TABLE_DATA
DBMS_USER_CERTS
Object Privileges
System Privileges
Tables
What's New In 21c
What's New In 23c