# [26ai] Blockchain & Immutable table *If you prefer to read in Spanish* [***Spanish version***](https://dbaenlasombra.com/26ai-blockchain-and-immutable-table)*.* Today, we’re going to talk about **Blockchain tables** and **Immutable tables**. Both were introduced in Oracle in order to protect against unauthorized data modification by external user, you can only **insert rows**. Unlike **Immutable tables**, **Blockchain tables** include a **cryptographic hash** for every row. Even though it’s not a feature in **26ai,** it was available in **21c**. However, several new features have been introduced in the latest release such as: * Adding and removing columns in **Blockchain** and **Immutable tables**, it’s always specifically **VERSION v2** of these tables. Below you can see how it works: ```sql SQL> CREATE IMMUTABLE TABLE T_TEST_VRS_1 ( TC_ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY ) no drop until 2 days idle no delete until 16 days after insert VERSION "v1"; Table created. SQL> ALTER TABLE T_TEST_VRS_1 ADD (FECHA DATE); ERROR at line 1: ORA-05715: operation not allowed on the blockchain or immutable table Help: https://docs.oracle.com/error-help/db/ora-05715/ SQL> CREATE IMMUTABLE TABLE T_TEST_VRS_2 ( TC_ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY ) no drop until 2 days idle no delete until 16 days after insert VERSION "v2"; SQL> ALTER TABLE T_TEST_VRS_2 ADD (FECHA DATE); Table altered. ``` * **BLOCKCHAIN\_TABLE\_MAX\_NO\_DROP**, a new parameter that controls the amount of idle time the table must be inactive before it can be dropped. When the parameter has a value and a user creates a new table with **NO DROP UNTIL** n **DAYS IDLE** clause, the value of **n** must be less than or equal to the parameter. However, if the parameter is null, any value can be used in the clause. Rows can’t be deleted while it is within the specified retention period. The same applies to dropping the table. When the retention period expires, you can delete rows or drop the table. ### **Retention Period** In the following diagram, we can see the different options available in order to set up our tables: ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1770905284440/cf2328b6-9839-4ec6-bf50-d31b632c045b.png align="center") Table level: * **NO DROP**: The table can’t be dropped unless it’s empty. * **NO DROP UNTIL** n **DAYS IDLE**: If there are rows within the specified retention period, the table can’t be dropped. By default, it’s 0, although a minimal value of 16 is recommended. Row level: * **NO DELETE \[*locked*\]**: Each row is retained forever. La clausula **\[*locked*\]** especifica que no se puede modificar la retención. * **NO DELETE UNTIL** n **DAYS AFTER INSERT \[*locked*\]**: Cada fila no puede ser eliminada, es decir, esta protegida de la eliminación tantos días como especifiquemos en la cláusula n **DAYS**. Valor mínimo es 16 días. La clausula **\[*locked*\]** especifica que no se puede modificar la retención. ### **Restrictions** Be aware of the following points: * The following data types are not supported: *ROWID*, *UROWID, LONG, OBJECT TYPE, REF, VARRAY, NESTED TABLE, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, BFILE* y *XMLType.* * It can’t be *INDEX-ORGANIZED, ORGANIZATION CUBE, ORGANIZATION EXTERNAL* o *HIBRYD PARTITIONED*. So, let's get down to business! **Inmutable Table** ```sql SQL> CREATE IMMUTABLE TABLE T_CLIENTES ( TC_ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, TC_name VARCHAR2(100) NOT NULL, TC_email VARCHAR2(200), TC_created_at DATE DEFAULT SYSDATE ) no drop until 2 days idle no delete until 16 days after insert VERSION "v2"; Table created. ``` If we need to modify the retention period, we can do it as follows: ```sql SQL> ALTER TABLE T_CLIENTES NO DROP UNTIL 5 DAYS IDLE; Table altered. SQL> ALTER TABLE T_CLIENTES NO DELETE UNTIL 32 DAYS AFTER INSERT; Table altered. ``` Let’s add columns,as the table uses the **VERSION v2** clause: ```sql SQL> ALTER TABLE T_CLIENTES ADD (TC_PROVINCIA VARCHAR2(100)); ``` Let’s insert some data and try to modify it: ```sql SQL> r 1* Select * From t_Clientes TC_ID TC_NAME TC_EMAIL TC_CREATE TC_PROVINCIA ---------- -------------------- -------------------- --------- ---------------------------------------------------------------------------------------------------- 1 Iker San San@demo.local 11-FEB-26 2 Juan Perro Perro@demo.local 11-FEB-26 3 Carla Marq Marq@demo.local 11-FEB-26 SQL> Update t_Clientes Set Tc_Provincia='VALLADOLID' where tc_id = 3; Update t_Clientes Set Tc_Provincia='VALLADOLID' where tc_id = 3 * ERROR at line 1: ORA-05715: operation not allowed on the blockchain or immutable table Help: https://docs.oracle.com/error-help/db/ora-05715/ ``` As we can see, we can’t modify the information, we can only insert new registers. ### **Blockchain Table** ```sql SQL> CREATE BLOCKCHAIN TABLE T_PRODUCTOSTIENDA ( TP_id NUMBER, TP_NOMBRE VARCHAR2(100), TP_PRICE FLOAT ) NO DROP UNTIL 14 DAYS IDLE NO DELETE UNTIL 30 DAYS AFTER INSERT HASHING USING "SHA2_512" WITH ROW VERSION AND USER CHAIN T_PRODUCTOS_VALIDATION (TP_id) VERSION "v2"; Table created. ``` If we need to modify the retention period, we can do it as follows: ```sql SQL> ALTER TABLE T_PRODUCTOSTIENDA NO DROP UNTIL 16 DAYS IDLE; Table altered. SQL> ALTER TABLE T_PRODUCTOSTIENDA NO DELETE UNTIL 32 DAYS AFTER INSERT; Table altered. ``` Let’s add columns,as the table uses the **VERSION v2** clause: ```sql SQL> ALTER TABLE T_PRODUCTOSTIENDA ADD (TC_CANTIDAD INTEGER); ``` Let’s insert some data and try to modify it: ```sql SQL> Select * From T_PRODUCTOSTIENDA; TP_ID TP_NOMBRE TP_PRICE TC_CANTIDAD ---------- -------------------- ---------- ----------- 1 PERAS 1 PERAS JULIANAS 2 NARANJAS 3 MANGOS SQL> UPDATE T_PRODUCTOSTIENDA SET TP_PRICE = 9; UPDATE T_PRODUCTOSTIENDA SET TP_PRICE = 9 * ERROR at line 1: ORA-05715: operation not allowed on the blockchain or immutable table Help: https://docs.oracle.com/error-help/db/ora-05715/ ``` As we can see, we can’t modify the information, we can only insert new registers. If you look at the SQL output, we can see two rows with the same tp\_id, but…which one was inserted last? In this type of table, Oracle automatically generates a view called **<TABLE\_NAME>\_LAST$** in order to display the latest version of each record. ```sql SQL> Select * From T_PRODUCTOSTIENDA_LAST$; TP_ID TP_NOMBRE TP_PRICE ---------- -------------------- ---------- 1 PERAS JULIANAS 2 NARANJAS 3 MANGOS ``` In future articles, we will cover the topic of deletion based on the examples we’ve used. Looking forward to seeing you in the next article :)