Oracle Immutable Table available from 19.11 (Apr2021 RU) onwards

 Oracle 19c & 21c required (19.11 onwards with minimal compatible 19.11)

[oracle@ol8 ~]$ sql donghua/password@pdb1
SQLcl: Release 20.4 Production on Tue Apr 27 20:06:12 2021
Copyright (c) 1982, 2021, Oracle.  All rights reserved.
Last Successful login time: Tue Apr 27 2021 20:06:13 +08:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.11.0.0.0

SQL> select value from v$parameter where name='compatible';

     VALUE
__________
19.11.0

Create & Drop Immutable table

SQL> CREATE IMMUTABLE TABLE trade_ledger (id NUMBER, luser VARCHAR2(40), value NUMBER)
  2         NO DROP UNTIL 40 DAYS IDLE
  3         NO DELETE UNTIL 100 DAYS AFTER INSERT;
Immutable TABLE created.

SQL> -- Empty immutable table or idle > 40 days can be dropped

SQL> drop table trade_ledger;
Table TRADE_LEDGER dropped.

Create immutable table & populate data

SQL> CREATE IMMUTABLE TABLE trade_ledger (id NUMBER, luser VARCHAR2(40), value NUMBER)
  2         NO DROP UNTIL 40 DAYS IDLE
  3         NO DELETE UNTIL 100 DAYS AFTER INSERT;
Immutable TABLE created.

SQL> insert into trade_ledger values (1,'donghua',100);
1 row inserted.

SQL> commit;
Commit complete.

Verify these prohibit actions

SQL> update trade_ledger set value=200 where id=1;

Error starting at line : 1 in command -
update trade_ledger set value=200 where id=1
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-05715: operation not allowed on the blockchain or immutable table

SQL> delete from trade_ledger;

Error starting at line : 1 in command -
delete from trade_ledger
Error at Command Line : 1 Column : 13
Error report -
SQL Error: ORA-05715: operation not allowed on the blockchain or immutable table

SQL> truncate table trade_ledger;

Error starting at line : 1 in command -
truncate table trade_ledger
Error report -
ORA-05715: operation not allowed on the blockchain or immutable table

SQL> drop table trade_ledger;

Error starting at line : 1 in command -
drop table trade_ledger
Error report -
ORA-05723: drop blockchain or immutable table TRADE_LEDGER not allowed

Query Immutable table data dictionary

SQL> select * from  user_immutable_tables;

     TABLE_NAME    ROW_RETENTION    ROW_RETENTION_LOCKED    TABLE_INACTIVITY_RETENTION
_______________ ________________ _______________________ _____________________________
TRADE_LEDGER                 100 NO                                                 40

Reference URL: https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-tables.html#GUID-21D76E99-96BF-4F07-89C0-5C6E248E424F

Liquibase with Sqlcl doesn't support BLOB data type for "lb data" command

SQL> create table t1 (id number, name varchar2(20), clob_col clob, blob_col blob); Table created. SQL> insert into t1 values (1, 'Donghua', '{"id":1,"first_name":"Iron","last_name":"Man"}', utl_raw.cast_to_raw('Donghua')); 1 row created. SQL> commit; Commit complete. SQL> info t1; TABLE: T1 LAST ANALYZED: ROWS : SAMPLE SIZE : INMEMORY :DISABLED COMMENTS : Columns NAME DATA TYPE NULL DEFAULT COMMENTS ID NUMBER Yes NAME VARCHAR2(20 BYTE) Yes CLOB_COL CLOB Yes BLOB_COL BLOB Yes SQL> select * from t1; ID NAME CLOB_COL BLOB_COL _____ __________ _________________________________________________ _________________ 1 Donghua {"id":1,"first_name":"Iron","last_name":"Man"} 446F6E67687561

 SQL> lb version

Liquibase version: 4.1.1 Extension Version: 20.4.2.0 SQL> lb data -object t1 Action successfully completed please review created file data1.xml

data1.xml indicates that BLOB_COL is "UNSUPPORTED FOR DIFF: BINARY DATA"

<?xml version="1.1" encoding="UTF-8" standalone="no"?> <databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocati on="http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangel og/dbchangelog-4.1.xsd"> <changeSet author="oracle (generated)" id="1619509111980-1"> <insert tableName="T1"> <column name="ID" valueNumeric="1"/> <column name="NAME" value="Donghua"/> <column name="CLOB_COL" value="{&quot;id&quot;:1,&quot;first_name&quot;:&quot;Iron&quot;,&quot;last_name&quot;:&quot;Man&quot;}"/> <column name="BLOB_COL" valueComputed="UNSUPPORTED FOR DIFF: BINARY DATA"/> </insert> </changeSet> </databaseChangeLog>